public static void CloseMonth(DataRow DRCurrentMonth) { try { cols = "Year varchar(50),Month varchar(50),FromDate datetime,ToDate datetime,Restaurant_ID int,Kitchen_ID int,Item_ID varchar(50),Qty bigint,Cost float"; Classes.CreateTable("BeginningEndingMonth", cols); where = "Month = '" + DRCurrentMonth["Month"].ToString() + "' AND Year = '" + DRCurrentMonth["Year"].ToString() + "'"; Classes.DeleteRows(where, "BeginningEndingMonth"); DataTable Items = Classes.RetrieveData("Code", "Setup_Items"); DataTable DTTop = new DataTable(); DataTable Kitchens = Classes.RetrieveData("*", "Setup_Kitchens"); string Dvalues = "('" + DRCurrentMonth["Year"] + "','" + DRCurrentMonth["Month"].ToString() + "','" + Convert.ToDateTime(DRCurrentMonth["From"]).ToString(Classes.sysDateTimeFormat) + "','" + Convert.ToDateTime(DRCurrentMonth["To"]).ToString(Classes.sysDateTimeFormat) + "'"; foreach (DataRow KitName in Kitchens.Rows) { values = ""; string Dkitchen = ",'" + KitName["Name"].ToString() + "'"; for (int i = 0; i < Items.Rows.Count; i++) { where = " _Date <= '" + Convert.ToDateTime(DRCurrentMonth["To"]).ToString(Classes.sysDateTimeFormat) + "' AND Item_ID = '" + Items.Rows[i][0].ToString() + "' AND KitchenName = '" + KitName["Name"].ToString() + "' order by _DATE DESC"; DTTop = Classes.RetrieveData("top 1 * ", where, "TransActions"); if (DTTop.Rows.Count != 0) { Qty = double.Parse(DTTop.Rows[0]["Current_Qty"].ToString()); Cost = double.Parse(DTTop.Rows[0]["CurrentCost"].ToString()); } else { Qty = 0; Cost = 0; } string DQtyCost = ",'" + Items.Rows[i][0].ToString() + "','" + Qty.ToString() + "','" + Cost.ToString() + "'"; string RestKitchen = ",'" + KitName["RestaurantID"].ToString() + "','" + KitName["Code"].ToString() + "'"; values += Dvalues + RestKitchen + DQtyCost + "),"; if (i % 999 == 0 || i == Items.Rows.Count - 1) { Classes.InsertRow("BeginningEndingMonth", values.Substring(1, values.Length - 3)); values = ""; } } } //MessageBox.Show("Month Closed Successfully"); } catch (Exception ee) { MessageBox.Show(ee.ToString()); } }
private void load_years() { string cols = "Year varchar(50),Month varchar(50),[Month Type] varchar(50),[From] date,[To] date,isClosed bit"; Classes.CreateTable("Setup_Fiscal_Period", cols); DataTable Years = Classes.RetrieveData("DISTINCT(Year)", "Setup_Fiscal_Period"); foreach (DataRow year in Years.Rows) { CBCreatedYears.Items.Add(year[0]); } }
private static void CalculateQty(DataRow DRCurrentMonth, DataTable DTPreviousMonth) { string TheColumns = "_DATE datetime,Restaurant_ID int,Kitchen_ID int,KitchenName varchar(50),Trantype varchar(50),ID varchar(50)"; TheColumns += ",Item_ID varchar(50),ItemName varchar(50),Qty float,Current_Qty float,Cost float,CurrentCost float"; Classes.CreateTable("TransActions", TheColumns); BETable = Classes.RetrieveStoredWithParamaeters("SPTransActions", "@StartDate,@EndDate", DRCurrentMonth["From"].ToString() + ',' + DRCurrentMonth["To"].ToString()); where = "_DATE between '" + Convert.ToDateTime(DRCurrentMonth["From"].ToString()).ToString(Classes.sysDateTimeFormat) + "' AND '" + Convert.ToDateTime(DRCurrentMonth["To"].ToString()).ToString(Classes.sysDateTimeFormat) + "'"; Classes.DeleteRows(where, "TransActions"); for (int k = 0; k < BETable.Rows.Count; k++) { if (k == 0) { GetPrevQty(k, DTPreviousMonth); } else if (BETable.Rows[k]["Trantype"].ToString() == "Adjactment") { BETable.Rows[k]["Qty"] = (double.Parse(BETable.Rows[k]["Current_Qty"].ToString()) - double.Parse(BETable.Rows[k - 1]["Current_Qty"].ToString())).ToString(); string TempWhere = "Adjacment_ID = '" + BETable.Rows[k]["ID"].ToString() + "' And Item_ID = '" + BETable.Rows[k]["Item_ID"].ToString() + "'"; Classes.UpdateRow("Qty,Variance", BETable.Rows[k - 1]["Current_Qty"].ToString() + "," + BETable.Rows[k]["Qty"].ToString(), TempWhere, "Adjacment_Items"); Insert(k); } else if (BETable.Rows[k]["Item_ID"].ToString() == BETable.Rows[k - 1]["Item_ID"].ToString() && BETable.Rows[k]["KitchenName"].ToString() == BETable.Rows[k - 1]["KitchenName"].ToString()) { BETable.Rows[k]["Current_Qty"] = (double.Parse(BETable.Rows[k]["Current_Qty"].ToString()) + double.Parse(BETable.Rows[k - 1]["Current_Qty"].ToString())).ToString(); Insert(k); } else { GetPrevQty(k, DTPreviousMonth); } } }