private void ClosingProcessRun()
{
DateTime now = DateTime.Now;
if (sqlite_conn.State == ConnectionState.Closed)
{
sqlite_conn.Open();
}
SQLiteCommand sqlite_cmd;
SQLiteDataReader sqlite_datareader;
string week = now.ToString("ddd");
string currentTime = now.ToString("HH:mm");
string storeEndqurey = "SELECT * FROM " + constants.tbNames[6]; // store end time checking
sqlite_cmd = sqlite_conn.CreateCommand();
sqlite_cmd.CommandText = storeEndqurey;
sqlite_datareader = sqlite_cmd.ExecuteReader();
while (sqlite_datareader.Read())
{
if (!sqlite_datareader.IsDBNull(0))
{
if (week == "Sat")
{
storeEndTime = (sqlite_datareader.GetString(6)).Split('/')[1];
}
else if (week == "Sun")
{
storeEndTime = (sqlite_datareader.GetString(6)).Split('/')[2];
}
else
{
storeEndTime = (sqlite_datareader.GetString(6)).Split('/')[0];
}
}
}
sumDayTime1 = constants.sumDayTimeStart(storeEndTime).AddDays(-1);
sumDayTime2 = constants.sumDayTimeEnd(storeEndTime).AddDays(-1);
sumDate = constants.sumDate(storeEndTime);
sumDate = Convert.ToDateTime(sumDate).AddDays(-1).ToString("yyyy-MM-dd");
string saleTBquery = "SELECT prdName, prdPrice, sum(prdAmount), prdRealID FROM " + constants.tbNames[3] + " WHERE saleDate>=@sumDayTime1 AND saleDate<=@sumDayTime2 GROUP BY prdRealID";
sqlite_cmd = sqlite_conn.CreateCommand();
sqlite_cmd.CommandText = saleTBquery;
sqlite_cmd.Parameters.AddWithValue("@sumDayTime1", sumDayTime1);
sqlite_cmd.Parameters.AddWithValue("@sumDayTime2", sumDayTime2);
sqlite_datareader = sqlite_cmd.ExecuteReader();
int j = 0;
while (sqlite_datareader.Read())
{
if (!sqlite_datareader.IsDBNull(0))
{
string prdName = sqlite_datareader.GetString(0);
int prdPrice = sqlite_datareader.GetInt32(1);
int prdAmount = sqlite_datareader.GetInt32(2);
int prdTotalPrice = prdPrice * prdAmount;
int prdID = sqlite_datareader.GetInt32(3);
SQLiteCommand sqlite_cmds;
string sumQuery = "INSERT INTO " + constants.tbNames[7] + " (prdID, prdName, prdPrice, prdAmount, prdTotalPrice, sumDate) VALUES (@prdID, @prdName, @prdPrice, @prdAmount, @prdTotalPrice, @sumDate)";
sqlite_cmds = sqlite_conn.CreateCommand();
sqlite_cmds.CommandText = sumQuery;
sqlite_cmds.Parameters.AddWithValue("@prdID", prdID);
sqlite_cmds.Parameters.AddWithValue("@prdName", prdName);
sqlite_cmds.Parameters.AddWithValue("@prdPrice", prdPrice);
sqlite_cmds.Parameters.AddWithValue("@prdAmount", prdAmount);
sqlite_cmds.Parameters.AddWithValue("@prdTotalPrice", prdTotalPrice);
sqlite_cmds.Parameters.AddWithValue("@sumDate", sumDate);
sqlite_cmds.ExecuteNonQuery();
SQLiteCommand sqlite_cmdss;
string sumQuerys = "UPDATE " + constants.tbNames[3] + " SET sumFlag='true', sumDate=@sumDate WHERE saleDate>=@sumDayTime1 AND saleDate<=@sumDayTime2 and prdRealID=@realPrdID";
sqlite_cmdss = sqlite_conn.CreateCommand();
sqlite_cmdss.CommandText = sumQuerys;
sqlite_cmdss.Parameters.AddWithValue("@sumDate", sumDate);
sqlite_cmdss.Parameters.AddWithValue("@sumDayTime1", sumDayTime1);
sqlite_cmdss.Parameters.AddWithValue("@sumDayTime2", sumDayTime2);
sqlite_cmdss.Parameters.AddWithValue("@realPrdID", prdID);
sqlite_cmdss.ExecuteNonQuery();
j++;
}
}
sqlite_conn.Close();
string currentDir = Directory.GetCurrentDirectory();
DBClass dbClass = new DBClass();
dbClass.DBCopy(sqlite_conn, Path.Combine(currentDir, "SettingValue.db"), "SettingValue", constants.tbNames);
processState = false;
}
