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