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