private async void Button_Retrieve_Click(object sender, RoutedEventArgs e)
{
string query;
DataSet ds;
DataRow drow;
string itemCode2;
string remarks;
string remarks2;
try
{
query = $@"Select Id,Quantity,Remarks from ItemDetailss where Id='{ItemCode_Retrieve}'";
ds = await dc.Connection1Async(query);
drow = ds.Tables[0].Rows[0];
itemCode2 = drow.ItemArray.GetValue(0).ToString();
AddQuantity = int.Parse(drow.ItemArray.GetValue(1).ToString());
remarks2 = drow.ItemArray.GetValue(2).ToString();
int addQuantity1;
addQuantity1 = ItemQuantity + AddQuantity;
remarks = "The item: " + ItemCode_Retrieve + " , " + ItemQuantity + " was recorved by " + fullName + " with an ID No. " + userId + " as of " + date + " - " + time.ToString();
query = $@"update ItemDetailss set Quantity = '{addQuantity1}' , Remarks = '{remarks2}{remarks}' where Id = '{ItemCode_Retrieve}'";
dc.Connection2(query);
query = $@"Insert into
RemarksMessage(Id,Remarks)
values ('{ItemCode_Retrieve}','{remarks}') ";
dc.Connection2(query);
query = $@"Delete From RequestorVieww where RequestorNo='{RequestNo}'";
dc.Connection2(query);
Loading_User.Visibility = Visibility.Visible;
Icon.Kind = MahApps.Metro.IconPacks.PackIconMaterialKind.Feather;
TextBlock_Message.FontSize = 13;
TextBlock_Message.Text = "The item: " + ItemCode_Retrieve + " , " + ItemQuantity + " was recorved by " + fullName + " with an ID No. " + userId + " as of " + date + " - " + time.ToUpper();
status = userContent.itemRecovery;
}
catch
{
Loading_User.Visibility = Visibility.Visible;
Icon.Kind = MahApps.Metro.IconPacks.PackIconMaterialKind.DiskAlert;
TextBlock_Message.Text = "Item doesn't exist".ToUpper();
}
}
private async void Button_Search_Click(object sender, RoutedEventArgs e)
{
dc = new database_connection();
DataSet ds;
DataRow drow;
string query;
query = $@"select Id,Firstname,Lastname,Department from UserLogin where Id like'%{TextBox_Search.Text}%' or Firstname like '%{TextBox_Search.Text}%' or Lastname like '%{TextBox_Search.Text}%'";
ds = await dc.Connection1Async(query);
int count;
count = ds.Tables[0].Rows.Count;
removeText();
if (count == 0)
{
Loading_User.Visibility = Visibility.Visible;
Icon.Kind = MahApps.Metro.IconPacks.PackIconMaterialKind.MagnifyMinusOutline;
TextBlock_Message.Text = "Search doesn't exist".ToUpper();
}
else
{
drow = ds.Tables[0].Rows[0];
TextBox_Id.Text = drow.ItemArray.GetValue(0).ToString();
TextBox_FirstName.Text = drow.ItemArray.GetValue(1).ToString();
TextBox_LastName.Text = drow.ItemArray.GetValue(2).ToString();
TextBox_Department.Text = drow.ItemArray.GetValue(3).ToString();
}
}
private async Task <String> GetNewReferenceNumber()
{
string query;
DataSet ds;
DataRow drow;
database_connection dc = new database_connection();
query = $@"
select s_prefix + right(('0000000000' + convert(varchar(10),d_last_num + 1)),8)
,d_last_num + 1
from ItemId
";
ds = await dc.Connection1Async(query);
drow = ds.Tables[0].Rows[0];
string new_transaction_number = drow.ItemArray.GetValue(0).ToString();
string d_new_num = drow.ItemArray.GetValue(1).ToString();
query = $@"
UPDATE ItemId
SET d_last_num = '{d_new_num}'
,IdItem = '{new_transaction_number}'
";
dc.Connection2(query);
return(new_transaction_number);
}
async void refresh()
{
string query;
DataSet ds;
DataRow drow;
dc = new database_connection();
try
{
query = $@"
select RequestorNo
,ItemDescription
,Quantity
,Unit
,CONVERT(VARCHAR(12), DateIssued, 107) AS [Mon DD, YYYY]
,Convert(VARCHAR(12), Timee, 0 ) as output, 0 as style
,IdOfRequestor
,ItemCode
from RequestorVieww
where IdOfRequestor ='{userId}'
";
Loading_User.Visibility = Visibility.Visible;
ds = await dc.Connection1Async(query);
Loading_User.Visibility = Visibility.Collapsed;
int ctr, total;
total = ds.Tables[0].Rows.Count;
itemselected_source.Clear();
for (ctr = 0; ctr < total; ctr++)
{
drow = ds.Tables[0].Rows[ctr];
itemselected_source.Add(new LogBook
{
RequestNo = drow.ItemArray.GetValue(0).ToString()
,
ItemDescription = drow.ItemArray.GetValue(1).ToString()
,
ItemQuantity = int.Parse(drow.ItemArray.GetValue(2).ToString())
,
ItemUnit = drow.ItemArray.GetValue(3).ToString()
,
Datee = drow.ItemArray.GetValue(4).ToString()
,
Timee = drow.ItemArray.GetValue(5).ToString()
,
ItemCode = drow.ItemArray.GetValue(8).ToString()
});
}
}
catch (Exception ex)
{
Loading_User.Visibility = Visibility.Visible;
Icon.Kind = MahApps.Metro.IconPacks.PackIconMaterialKind.MessageAlert;
TextBlock_Message.Text = ex.Message.ToUpper();
}
}
private async void TextBox_Search_TextChanged(object sender, TextChangedEventArgs e)
{
string query;
DataSet ds;
DataRow drow;
database_connection dc = new database_connection();
try
{
query = $@"select Id,
ItemDescription
,Quantity
,Unit
,CONVERT(VARCHAR(12), DateAcquired, 107) AS [Mon DD, YYYY]
,Price
,TotalCost
,Remarks
from ItemDetailss
where Id like '%{TextBox_Search.Text}%' or ItemDescription like '%{TextBox_Search.Text}%' or Unit like '%{TextBox_Search.Text}%' or DateAcquired like '%{TextBox_Search.Text}%'";
ds = await dc.Connection1Async(query);
int ctr, total;
total = ds.Tables[0].Rows.Count;
itemdetailss.Clear();
for (ctr = 0; ctr < total; ctr++)
{
drow = ds.Tables[0].Rows[ctr];
itemdetailss.Add(new ItemDetailss
{
TransactionId = drow.ItemArray.GetValue(0).ToString()
,
ItemDescription = drow.ItemArray.GetValue(1).ToString()
,
ItemQuantity = int.Parse(drow.ItemArray.GetValue(2).ToString())
,
ItemUnit = drow.ItemArray.GetValue(3).ToString()
,
DateAcquired = drow.ItemArray.GetValue(4).ToString()
,
Price = double.Parse(drow.ItemArray.GetValue(5).ToString())
,
TotalCost = double.Parse(drow.ItemArray.GetValue(6).ToString())
,
Remarks = drow.ItemArray.GetValue(7).ToString()
});
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
async void refresh()
{
string query;
DataSet ds;
DataRow drow;
database_connection dc = new database_connection();
int ctr, total;
try
{
query = $@"select Id
,ItemDescription
,Quantity
,Unit
,CONVERT(VARCHAR(12), DateAcquired, 107) AS [Mon DD, YYYY]
,Price
,TotalCost
,Remarks
from ItemDetailss";
Loading_User.Visibility = Visibility.Visible;
ds = await dc.Connection1Async(query);
Loading_User.Visibility = Visibility.Collapsed;
total = ds.Tables[0].Rows.Count;
itemdetailss.Clear();
for (ctr = 0; ctr < total; ctr++)
{
drow = ds.Tables[0].Rows[ctr];
itemdetailss.Add(new ItemDetailss
{
TransactionId = drow.ItemArray.GetValue(0).ToString()
,
ItemDescription = drow.ItemArray.GetValue(1).ToString()
,
ItemQuantity = int.Parse(drow.ItemArray.GetValue(2).ToString())
,
ItemUnit = drow.ItemArray.GetValue(3).ToString()
,
DateAcquired = drow.ItemArray.GetValue(4).ToString()
,
Price = double.Parse(drow.ItemArray.GetValue(5).ToString())
,
TotalCost = double.Parse(drow.ItemArray.GetValue(6).ToString())
,
Remarks = drow.ItemArray.GetValue(7).ToString()
});
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private async void Button_Search_Click(object sender, RoutedEventArgs e)
{
DataSet ds;
DataRow drow;
database_connection dc = new database_connection();
string query;
requestor_source.Clear();
query = $@"select RequestorNo
,NameOfRequestor,
IdOfRequestor
,Department
,ItemCode
,ItemDescription
,Quantity
,Unit
,CONVERT(VARCHAR(12), DateIssued, 107) AS [Mon DD, YYYY]
,Convert(VARCHAR(12), Timee, 0 ) as output, 0 as style
from RequestorVieww where RequestorNo like '%{TextBox_Search.Text}%'";
Loading_User.Visibility = Visibility.Visible;
ds = await dc.Connection1Async(query);
Loading_User.Visibility = Visibility.Collapsed;
int ctr, total;
total = ds.Tables[0].Rows.Count;
for (ctr = 0; ctr < total; ctr++)
{
drow = ds.Tables[0].Rows[ctr];
requestor_source.Add(new Requestor
{
RequestorNo = drow.ItemArray.GetValue(0).ToString()
,
NameOfRequestor = drow.ItemArray.GetValue(1).ToString()
,
IdOfRequestor = drow.ItemArray.GetValue(2).ToString()
,
Department = drow.ItemArray.GetValue(3).ToString()
,
ItemCode = drow.ItemArray.GetValue(4).ToString()
,
ItemDescription = drow.ItemArray.GetValue(5).ToString()
,
Quantity = drow.ItemArray.GetValue(6).ToString()
,
Unit = drow.ItemArray.GetValue(7).ToString()
,
DateIssued = drow.ItemArray.GetValue(8).ToString()
,
Time = drow.ItemArray.GetValue(9).ToString()
});
}
}
async void refresh()
{
dc = new database_connection();
DataSet ds;
DataRow drow;
string query;
begin_source.Clear();
query = $@"Select Monthh, Yearr
,Id
,ItemDescription
,Quantity
,Unit
,Price
,TotalCost
,GeneratedId
from IssuanceInventory
where Monthh = '{ComboBox_Month.Text}'
and Yearr = '{ComboBox_Year.Text}'";
Loading_User.Visibility = Visibility.Visible;
ds = await dc.Connection1Async(query);
Loading_User.Visibility = Visibility.Collapsed;
int ctr, total;
total = ds.Tables[0].Rows.Count;
for (ctr = 0; ctr < total; ctr++)
{
drow = ds.Tables[0].Rows[ctr];
begin_source.Add(new Begin
{
Month = drow.ItemArray.GetValue(0).ToString()
,
Year = drow.ItemArray.GetValue(1).ToString()
,
ItemCode = drow.ItemArray.GetValue(2).ToString()
,
ItemDescription = drow.ItemArray.GetValue(3).ToString()
,
Quantity = drow.ItemArray.GetValue(4).ToString()
,
Unit = drow.ItemArray.GetValue(5).ToString()
,
Price = drow.ItemArray.GetValue(6).ToString()
,
TotalCost = drow.ItemArray.GetValue(7).ToString()
,
GeneratedId = drow.ItemArray.GetValue(8).ToString()
});
}
}
async void enter()
{
database_connection dc = new database_connection();
DataSet ds;
string query;
try
{
query = $@"select Id,Pass from AdminLogin where Id='" + TextBox_IdNo.Text + "'and Pass='******'";
Loading_User.Visibility = Visibility.Visible;
ds = await dc.Connection1Async(query);
Loading_User.Visibility = Visibility.Hidden;
int count;
count = ds.Tables[0].Rows.Count;
if (count > 0)
{
MainWindow mw = new MainWindow();
success s = new success();
this.Visibility = Visibility.Hidden;
AdminContent ac = new AdminContent();
admin_drawhost ad = new admin_drawhost();
foreach (Window window in Application.Current.Windows)
{
if (window.GetType() == typeof(MainWindow))
{
(window as MainWindow).Rectangle_MainContent.Visibility = Visibility.Hidden;
(window as MainWindow).Rectangle_MainWindow.Visibility = Visibility.Hidden;
(window as MainWindow).Drawer_HostContent.Content = ad;
(window as MainWindow).UserContent.Content = ac;
}
}
}
else
{
Loading_UserInform.Visibility = Visibility.Visible;
Icon2.Kind = MahApps.Metro.IconPacks.PackIconMaterialKind.MessageAlert;
TextBlock_Message2.Text = "The ID No. and Password you've \n entered doesn't match".ToString();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private async void UserControl_Loaded(object sender, RoutedEventArgs e)
{
database_connection dc = new database_connection();
string query;
DataSet ds;
DataRow drow;
int ctr, total;
string mess;
string dayBool;
try
{
query = $@"Select Remarks from RemarksMessage Group By Remarks ";
Loading_User.Visibility = Visibility.Visible;
ds = await dc.Connection1Async(query);
Loading_User.Visibility = Visibility.Collapsed;
total = ds.Tables[0].Rows.Count;
for (ctr = 0; ctr < total; ctr++)
{
drow = ds.Tables[0].Rows[ctr];
mess = drow.ItemArray.GetValue(0).ToString();
MessageBox.Show(mess);
// Loading_User.Visibility = Visibility.Visible;
// TextBlock_Message.FontSize = 10;
//.Kind = MahApps.Metro.IconPacks.PackIconMaterialKind.MessageAlert;
// TextBlock_Message.Text = mess.ToUpper();
}
if (dayAlert == "01")
{
query = $@"Select Id,DayBool From MonthlyAlert where Id = '1'";
ds = await dc.Connection1Async(query);
drow = ds.Tables[0].Rows[0];
dayBool = drow.ItemArray.GetValue(1).ToString();
if (dayBool == "T")
{
if (monthAlert == "01")
{
query = $@"insert IssuanceInventory(Id,ItemDescription,Unit,Monthh,Yearr,Quantity,Price,TotalCost)
select Itemcode,ItemDescription,Unit,Monthh,yearr,sum(Quantity),sum(Price),sum(TotalCost) from RequestorVieww group by Itemcode, ItemDescription,Unit ,Monthh,yearr";
ds = await dc.Connection1Async(query);
query = $@"insert EndingInventory(Id,ItemDescription,Unit,Quantity,Price,TotalCost,Monthh,Yearr)
select Id,ItemDescription,Unit,Quantity,Price,TotalCost,DATEADD(ms,-2,DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)),Year(GetDate()) -1 from ItemDetailss ";
ds = await dc.Connection1Async(query);
query = $@"insert BeginningInventory(Id,ItemDescription,Unit,Quantity,Price,TotalCost,Monthh,Yearr)
select Id,ItemDescription,Unit,Quantity,Price,TotalCost,DATENAME(month, GETDATE()),Year(GetDate()) from ItemDetailss ";
ds = await dc.Connection1Async(query);
query = $@"Update MonthlyAlert Set DayBool = 'F' where Id ='1'";
dc.Connection2(query);
MessageBox.Show("Monthly Update");
}
if (monthAlert != "01")
{
query = $@"insert IssuanceInventory(Id,ItemDescription,Unit,Monthh,Yearr,Quantity,Price,TotalCost)
select Itemcode,ItemDescription,Unit,Monthh,yearr,sum(Quantity),sum(Price),sum(TotalCost) from RequestorVieww group by Itemcode, ItemDescription,Unit ,Monthh,yearr";
ds = await dc.Connection1Async(query);
query = $@"insert EndingInventory(Id,ItemDescription,Unit,Quantity,Price,TotalCost,Monthh,Yearr)
select Id,ItemDescription,Unit,Quantity,Price,TotalCost,DATEADD(ms,-2,DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)),Year(GetDate()) from ItemDetailss ";
ds = await dc.Connection1Async(query);
query = $@"insert BeginningInventory(Id,ItemDescription,Unit,Quantity,Price,TotalCost,Monthh,Yearr,DateAcquired)
select Id,ItemDescription,Unit,Quantity,Price,TotalCost,DATENAME(month, GETDATE()),Year(GetDate()),GetDate() from ItemDetailss ";
ds = await dc.Connection1Async(query);
query = $@"Update MonthlyAlert Set DayBool = 'F' where Id ='1'";
dc.Connection2(query);
MessageBox.Show("Monthly Update");
}
}
}
if (dayAlert != "01")
{
query = $@"Update MonthlyAlert Set DayBool = 'T' where Id ='1'";
dc.Connection2(query);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private async void Button_Save_Click(object sender, RoutedEventArgs e)
{
DataSet ds;
try
{
if (status == statusAdminContent.Add)
{
if (TextBox_ItemDescription.Text == "" | TextBox_Quantity.Text == "" | TextBox_Unit.Text == "" | TextBox_Price.Text == "" | int.Parse(TextBox_Price.Text) <= 0 | int.Parse(TextBox_Quantity.Text) <= 0)
{
Loading_User.Visibility = Visibility.Visible;
Icon.Kind = MahApps.Metro.IconPacks.PackIconMaterialKind.AccountCheck;
TextBlock_Message.Text = "Complete the Information".ToUpper();
}
else
{
Loading_User.Visibility = Visibility.Visible;
Icon.Kind = MahApps.Metro.IconPacks.PackIconMaterialKind.ContentSave;
TextBlock_Message.Text = "saved records".ToUpper();
string query;
dc = new database_connection();
string s_new_number = await GetNewReferenceNumber();
query = $@"
insert into ItemDetailss (Id,ItemDescription
,Quantity
,Unit
,DateAcquired
,Price
,TotalCost
)
values('{s_new_number}'
,'{TextBox_ItemDescription.Text}'
,'{TextBox_Quantity.Text}'
,'{TextBox_Unit.Text}'
,'{date}'
,'{TextBox_Price.Text}'
,'{TextBox_TotalCost.Text}'
)";
ds = await dc.Connection1Async(query);
query = $@"Insert into
BeginningInventory
(Monthh
,Yearr
,Id
,ItemDescription
,Quantity
,Unit
,Price
,TotalCost
,DateAcquired
)
Values ('{month}'
,'{year}'
,'{s_new_number}'
,'{TextBox_ItemDescription.Text}'
,'{TextBox_Quantity.Text}'
,'{TextBox_Unit.Text}'
,'{TextBox_Price.Text}'
,'{TextBox_TotalCost.Text}'
,'{date}')
";
dc.Connection2(query);
TextBox_TransactionId.Text = s_new_number;
status = statusAdminContent.Save;
}
}
else
{
Loading_User.Visibility = Visibility.Visible;
Icon.Kind = MahApps.Metro.IconPacks.PackIconMaterialKind.NewBox;
TextBlock_Message.Text = "Please click new button".ToUpper();
}
}
catch (Exception ex)
{
Loading_User.Visibility = Visibility.Visible;
Icon.Kind = MahApps.Metro.IconPacks.PackIconMaterialKind.Alert;
TextBlock_Message.Text = ex.Message.ToUpper();
}
}
async void enter()
{
database_connection dc = new database_connection();
DataSet ds;
DataRow drow;
string query;
try
{
query = $@"select Id,Pass,Firstname,Lastname,Department from UserLogin where Id='" + TextBox_IdNo.Text + "'and Pass='******'";
Loading_User.Visibility = Visibility.Visible;
ds = await dc.Connection1Async(query);
Loading_User.Visibility = Visibility.Hidden;
int count;
count = ds.Tables[0].Rows.Count;
if (count > 0)
{
drow = ds.Tables[0].Rows[0];
userId = drow.ItemArray.GetValue(0).ToString();
firstName = drow.ItemArray.GetValue(2).ToString();
lastName = drow.ItemArray.GetValue(3).ToString();
department = drow.ItemArray.GetValue(4).ToString();
fullName = firstName + " " + lastName;
MainWindow mw = new MainWindow();
success s = new success();
this.Visibility = Visibility.Hidden;
user_content uc = new user_content();
user_drawhost us = new user_drawhost();
foreach (Window window in Application.Current.Windows)
{
if (window.GetType() == typeof(MainWindow))
{
(window as MainWindow).Rectangle_MainContent.Visibility = Visibility.Hidden;
(window as MainWindow).Rectangle_MainWindow.Visibility = Visibility.Hidden;
(window as MainWindow).Drawer_HostContent.Content = us;
(window as MainWindow).UserContent.Content = uc;
}
}
}
else
{
Loading_UserInform.Visibility = Visibility.Visible;
Icon2.Kind = MahApps.Metro.IconPacks.PackIconMaterialKind.MessageAlert;
TextBlock_Message2.Text = "The ID No. and Password you've \n entered doesn't match".ToString();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private async void Button_Search_Click(object sender, RoutedEventArgs e)
{
string query;
DataSet ds;
DataRow drow;
dc = new database_connection();
try
{
query = $@"
select RequestorNo
,ItemDescription
,Quantity
,Unit
,CONVERT(VARCHAR(12), DateIssued, 107) AS [Mon DD, YYYY]
,Convert(VARCHAR(12), Timee, 0 ) as output, 0 as style
,ItemCode
,IdOfRequestor
from RequestorVieww
where (RequestorNo like '%{TextBox_Search.Text}%' or ItemDescription like '%{TextBox_Search.Text}%' or ItemCode like '%{TextBox_Search.Text}%' or DateIssued like '%{TextBox_Search.Text}%') and IdOfRequestor like '%{userId}%'
";
Loading_User.Visibility = Visibility.Visible;
ds = await dc.Connection1Async(query);
Loading_User.Visibility = Visibility.Collapsed;
int ctr, total;
total = ds.Tables[0].Rows.Count;
itemselected_source.Clear();
for (ctr = 0; ctr < total; ctr++)
{
drow = ds.Tables[0].Rows[ctr];
itemselected_source.Add(new LogBook
{
RequestNo = drow.ItemArray.GetValue(0).ToString()
,
ItemDescription = drow.ItemArray.GetValue(1).ToString()
,
ItemQuantity = int.Parse(drow.ItemArray.GetValue(2).ToString())
,
ItemUnit = drow.ItemArray.GetValue(3).ToString()
,
Datee = drow.ItemArray.GetValue(4).ToString()
,
Timee = drow.ItemArray.GetValue(5).ToString()
,
ItemCode = drow.ItemArray.GetValue(7).ToString()
});
}
status = userContent.search;
}
catch (Exception ex)
{
Loading_User.Visibility = Visibility.Visible;
Icon.Kind = MahApps.Metro.IconPacks.PackIconMaterialKind.MessageAlert;
TextBlock_Message.Text = ex.Message.ToUpper();
}
}