private void LoadPhysicalInventory(string valofResturant, string valofKitchen, string PhiID) { //Michael physicalinventoryID = PhiID; string Where = ""; double TotalCost = 0; DataTable PhysicalDetailsData = new DataTable(); DataTable AdjacmentDetails = new DataTable(); DataTable ItemsQtyAndCost = new DataTable(); DataTable ItemsName = new DataTable(); Where = string.Format("Inventory_ID='{0}'", PhiID); PhysicalDetailsData = Classes.RetrieveData("Inventory_Date,Comment,Blind,Resturant_ID,KitchenID", Where, "PhysicalInventory_tbl"); ValOfResturant = PhysicalDetailsData.Rows[0][3].ToString(); ValOfKitchen = PhysicalDetailsData.Rows[0][4].ToString(); Reasoncbx.Items.Add("Physical Inventory"); Reasoncbx.Text = "Physical Inventory"; Reasoncbx.IsEnabled = false; Adjacment_Date.Text = PhysicalDetailsData.Rows[0][0].ToString(); commenttxt.Text = PhysicalDetailsData.Rows[0][1].ToString(); Blind = Convert.ToBoolean(PhysicalDetailsData.Rows[0][2].ToString()); if (Blind == true) { AdjacmentDetails.Columns.Add("Code"); AdjacmentDetails.Columns.Add("Name"); AdjacmentDetails.Columns.Add("Name2"); AdjacmentDetails.Columns.Add("Qty"); AdjacmentDetails.Columns.Add("Phsycal Qty"); AdjacmentDetails.Columns.Add("Variance"); AdjacmentDetails.Columns.Add("Cost"); AdjacmentDetails.Columns.Add("Total Cost"); try { Where = string.Format("Inventory_ID='{0}'", PhiID); ItemsQtyAndCost = Classes.RetrieveData("Item_ID,Qty,InventoryQty,Variance,Cost", Where, "PhysicalInventory_Items"); for (int i = 0; i < ItemsQtyAndCost.Rows.Count; i++) { Where = string.Format("Code='{0}'", ItemsQtyAndCost.Rows[i][0].ToString()); ItemsName = Classes.RetrieveData("Name,Name2", Where, "Setup_Items"); double TotalItemsCost = (Convert.ToDouble(ItemsQtyAndCost.Rows[i][1].ToString()) * Convert.ToDouble(ItemsQtyAndCost.Rows[i][4].ToString())); TotalCost += TotalItemsCost; AdjacmentDetails.Rows.Add(ItemsQtyAndCost.Rows[i][0], ItemsName.Rows[0][0], ItemsName.Rows[0][1], ItemsQtyAndCost.Rows[i][1], ItemsQtyAndCost.Rows[i][2], ItemsQtyAndCost.Rows[i][3], ItemsQtyAndCost.Rows[i][4], TotalItemsCost); } for (int i = 0; i < AdjacmentDetails.Columns.Count; i++) { AdjacmentDetails.Columns[i].ReadOnly = true; } AdjacmentDetails.Columns["Phsycal Qty"].ReadOnly = false; } catch { } } else { AdjacmentDetails.Columns.Add("Code"); AdjacmentDetails.Columns.Add("Name"); AdjacmentDetails.Columns.Add("Name2"); AdjacmentDetails.Columns.Add("Qty"); AdjacmentDetails.Columns.Add("Cost"); AdjacmentDetails.Columns.Add("Total Cost"); try { Where = string.Format("Inventory_ID='{0}'", PhiID); ItemsQtyAndCost = Classes.RetrieveData("Item_ID,Qty,Cost", Where, "PhysicalInventory_Items"); for (int i = 0; i < ItemsQtyAndCost.Rows.Count; i++) { Where = string.Format("Code='{0}'", ItemsQtyAndCost.Rows[i][0].ToString()); ItemsName = Classes.RetrieveData("Name,Name2", Where, "Setup_Items"); double TotalItemsCost = (Convert.ToDouble(ItemsQtyAndCost.Rows[i][1].ToString()) * Convert.ToDouble(ItemsQtyAndCost.Rows[i][2].ToString())); TotalCost += TotalItemsCost; AdjacmentDetails.Rows.Add(ItemsQtyAndCost.Rows[i][0], ItemsName.Rows[0][0], ItemsName.Rows[0][1], ItemsQtyAndCost.Rows[i][1], ItemsQtyAndCost.Rows[i][2], TotalItemsCost); } for (int i = 0; i < AdjacmentDetails.Columns.Count; i++) { AdjacmentDetails.Columns[i].ReadOnly = true; } AdjacmentDetails.Columns["Qty"].ReadOnly = false; } catch { } } ItemsDGV.DataContext = AdjacmentDetails; NUmberOfItems.Text = AdjacmentDetails.Rows.Count.ToString(); Total_Price.Text = TotalCost.ToString(); }
private void Row_Changed(object sender, DataGridCellEditEndingEventArgs e) { if (e.Column.Header == "Qty") { DataTable Dat = ItemsDGV.DataContext as DataTable; for (int i = 0; i < Dat.Columns.Count; i++) { Dat.Columns[i].ReadOnly = false; } SqlConnection con = new SqlConnection(Classes.DataConnString); con.Open(); string ItemCode = (e.Row.Item as DataRowView).Row["Code"].ToString(); //string Qty = (e.EditingElement as TextBox).Text; try { float to_rest_Qty = 0; float to_rest_Cost = 0; float from_rest_Qty = 0; float from_rest_Cost = 0; DataTable TheValues = Classes.RetriveCostAndQty(Resturant.Text, From_Kitchen.Text, ItemCode); try { from_rest_Qty = float.Parse(TheValues.Rows[0][0].ToString()); from_rest_Cost = float.Parse(TheValues.Rows[0][1].ToString()); } catch { from_rest_Qty = 0; from_rest_Cost = 0; } TheValues = Classes.RetriveCostAndQty(Resturant.Text, To_Kitchen.Text, ItemCode); try { to_rest_Qty = float.Parse(TheValues.Rows[0][0].ToString()); to_rest_Cost = float.Parse(TheValues.Rows[0][1].ToString()); } catch { to_rest_Qty = 0; to_rest_Cost = 0; } Dat.Rows[e.Row.GetIndex()]["Qty"] = (float.Parse((e.EditingElement as TextBox).Text)).ToString(); Dat.Rows[e.Row.GetIndex()][From_Kitchen.Text + " Qty"] = (from_rest_Qty - float.Parse((e.EditingElement as TextBox).Text)).ToString(); Dat.Rows[e.Row.GetIndex()][From_Kitchen.Text + " Unit Cost"] = from_rest_Cost.ToString(); Dat.Rows[e.Row.GetIndex()][From_Kitchen.Text + " Total Cost"] = (from_rest_Cost * (from_rest_Qty - float.Parse((e.EditingElement as TextBox).Text))).ToString(); Dat.Rows[e.Row.GetIndex()][To_Kitchen.Text + " Qty"] = (to_rest_Qty + float.Parse((e.EditingElement as TextBox).Text)).ToString(); Dat.Rows[e.Row.GetIndex()][To_Kitchen.Text + " Unit Cost"] = (((to_rest_Cost * to_rest_Qty) + (float.Parse((e.EditingElement as TextBox).Text) * from_rest_Cost)) / (to_rest_Qty + (float.Parse((e.EditingElement as TextBox).Text)))).ToString(); Dat.Rows[e.Row.GetIndex()][To_Kitchen.Text + " Total Cost"] = (((to_rest_Cost * to_rest_Qty) + (float.Parse((e.EditingElement as TextBox).Text) * from_rest_Cost)) / (to_rest_Qty + (float.Parse((e.EditingElement as TextBox).Text))) * (to_rest_Qty + float.Parse((e.EditingElement as TextBox).Text))).ToString(); } catch (Exception ex) { MessageBox.Show(ex.ToString()); (e.EditingElement as TextBox).Text = ""; } finally { con.Close(); } try { double totalPrice = 0; for (int i = 0; i < ItemsDGV.Items.Count; i++) { try { totalPrice += (Convert.ToDouble(((DataRowView)ItemsDGV.Items[i]).Row.ItemArray[5]) * Convert.ToDouble(((DataRowView)ItemsDGV.Items[i]).Row.ItemArray[7])); } catch { } } NUmberOfItems.Text = (ItemsDGV.Items.Count).ToString(); Total_Price.Text = (totalPrice).ToString(); } catch { } for (int i = 0; i < Dat.Columns.Count; i++) { Dat.Columns[i].ReadOnly = true; } Dat.Columns["Qty"].ReadOnly = false; ItemsDGV.DataContext = Dat; } } //Done
} //Done //Physcial Inventory Load and Functions private void SaveThePhyscialAdjacment() { if (!DoSomeChecks()) { return; } SqlConnection con = new SqlConnection(Classes.DataConnString); con.Open(); if (Blind == false) { try { string FiledSelection = "Adjacment_ID,Adjacment_Num,Adjacment_Reason,Adjacment_Date,Comment,Resturant_ID,KitchenID,Create_Date,Post_Date,User_ID,WS,Total_Cost"; string Values = string.Format("'{0}',{1},(select Code From Setup_AdjacmentReasons_tbl where Name='{2}'),'{3}','{4}',{5},{6},GETDATE(),GETDATE(),'{7}','{8}','{9}'", Serial_Adjacment_NO.Text, Adjacment_NO.Text, Reasoncbx.Text, Convert.ToDateTime(Adjacment_Date.Text).ToString("MM-dd-yyyy"), commenttxt.Text, ValOfResturant, ValOfKitchen, MainWindow.UserID, Classes.WS, Total_Price.Text); Classes.InsertRow("Adjacment_tbl", FiledSelection, Values); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } try { for (int i = 0; i < ItemsDGV.Items.Count; i++) { string FiledSelection = "Adjacment_ID,Item_ID,Qty,AdjacmentableQty,Variance,Cost"; string Values = string.Format("'{0}','{1}','{2}','{3}','{4}','{5}'", Serial_Adjacment_NO.Text, (((DataRowView)ItemsDGV.Items[i]).Row.ItemArray[0]), Convert.ToDouble(((DataRowView)ItemsDGV.Items[i]).Row.ItemArray[3]), Convert.ToDouble(((DataRowView)ItemsDGV.Items[i]).Row.ItemArray[3]), "0", Convert.ToDouble(((DataRowView)ItemsDGV.Items[i]).Row.ItemArray[4])); Classes.InsertRow("Adjacment_Items", FiledSelection, Values); } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } try { for (int i = 0; i < ItemsDGV.Items.Count; i++) { string H = string.Format("Update Items set Qty={0}, Current_Cost={4}, Net_Cost=({4} * {0}) where ItemID = '{1}' and RestaurantID ={2} and KitchenID={3}", Convert.ToDouble(((DataRowView)ItemsDGV.Items[i]).Row.ItemArray[3]), (((DataRowView)ItemsDGV.Items[i]).Row.ItemArray[0]), ValOfResturant, ValOfKitchen, Convert.ToDouble(((DataRowView)ItemsDGV.Items[i]).Row.ItemArray[4])); SqlCommand cmd = new SqlCommand(H, con); cmd.ExecuteNonQuery(); H = string.Format("update ItemsYear set {0}={1},{2}={3} where ItemID='{4}' and Restaurant_ID='{5}' and Kitchen_ID='{6}' and Year='{7}'", MainWindow.MonthQty, Convert.ToDouble(((DataRowView)ItemsDGV.Items[i]).Row.ItemArray[3]), MainWindow.MonthCost, Convert.ToDouble(((DataRowView)ItemsDGV.Items[i]).Row.ItemArray[4]), (((DataRowView)ItemsDGV.Items[i]).Row.ItemArray[0]), ValOfResturant, ValOfKitchen, MainWindow.CurrentYear); cmd = new SqlCommand(H, con); cmd.ExecuteNonQuery(); } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } finally { MessageBox.Show("Edited Successfully"); } } else { try { string FiledSelection = "Adjacment_ID,Adjacment_Num,Adjacment_Reason,Adjacment_Date,Comment,Resturant_ID,KitchenID,Create_Date,Post_Date,User_ID,WS,Total_Cost"; string Values = string.Format("'{0}',{1},(select Code From Setup_AdjacmentReasons_tbl where Name='{2}'),'{3}','{4}',{5},{6},GETDATE(),GETDATE(),'{7}','{8}','{9}'", Serial_Adjacment_NO.Text, Adjacment_NO.Text, Reasoncbx.Text, Convert.ToDateTime(Adjacment_Date.Text).ToString("MM-dd-yyyy"), commenttxt.Text, ValOfResturant, ValOfKitchen, MainWindow.UserID, Classes.WS, Total_Price.Text); Classes.InsertRow("Adjacment_tbl", FiledSelection, Values); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } try { for (int i = 0; i < ItemsDGV.Items.Count; i++) { string FiledSelection = "Adjacment_ID,Item_ID,Qty,AdjacmentableQty,Variance,Cost"; string Values = string.Format("'{0}','{1}','{2}','{3}','{4}','{5}'", Serial_Adjacment_NO.Text, (((DataRowView)ItemsDGV.Items[i]).Row.ItemArray[0]), Convert.ToDouble(((DataRowView)ItemsDGV.Items[i]).Row.ItemArray[3]), ((DataRowView)ItemsDGV.Items[i]).Row.ItemArray[4], ((DataRowView)ItemsDGV.Items[i]).Row.ItemArray[5], Convert.ToDouble(((DataRowView)ItemsDGV.Items[i]).Row.ItemArray[6])); Classes.InsertRow("Adjacment_Items", FiledSelection, Values); } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } try { for (int i = 0; i < ItemsDGV.Items.Count; i++) { string H = string.Format("Update Items set Qty={0}, Current_Cost={4}, Net_Cost=({4} * {0}) where ItemID = '{1}' and RestaurantID ={2} and KitchenID={3}", Convert.ToDouble(((DataRowView)ItemsDGV.Items[i]).Row.ItemArray[4]), (((DataRowView)ItemsDGV.Items[i]).Row.ItemArray[0]), ValOfResturant, ValOfKitchen, Convert.ToDouble(((DataRowView)ItemsDGV.Items[i]).Row.ItemArray[6])); SqlCommand cmd = new SqlCommand(H, con); cmd.ExecuteNonQuery(); H = string.Format("update ItemsYear set {0}={1},{2}={3} where ItemID='{4}' and Restaurant_ID='{5}' and Kitchen_ID='{6}' and Year='{7}'", MainWindow.MonthQty, Convert.ToDouble(((DataRowView)ItemsDGV.Items[i]).Row.ItemArray[4]), MainWindow.MonthCost, Convert.ToDouble(((DataRowView)ItemsDGV.Items[i]).Row.ItemArray[6]), (((DataRowView)ItemsDGV.Items[i]).Row.ItemArray[0]), ValOfResturant, ValOfKitchen, MainWindow.CurrentYear); cmd = new SqlCommand(H, con); cmd.ExecuteNonQuery(); } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } finally { MessageBox.Show("Edited Successfully"); } } try { string s = string.Format("update PhysicalInventory_tbl set Inventory_Type='Closed' where Inventory_ID={0}", physicalinventoryID); SqlCommand cmd = new SqlCommand(s, con); cmd.ExecuteNonQuery(); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } }