private void OpenFileDialog(object sender, EventArgs e) { if (folderBrowserDialog1.ShowDialog() == DialogResult.OK) { if (sqlite_conn.State == ConnectionState.Open) { sqlite_conn.Close(); } try { string SourcePath = folderBrowserDialog1.SelectedPath; string DestinationPath = Directory.GetCurrentDirectory(); DirectoryInfo dir = new DirectoryInfo(SourcePath); FileInfo[] targetFile = dir.GetFiles(); string sourceFileName = ""; var dbName = ""; foreach (FileInfo files in targetFile) { if (files.Extension == ".db") { sourceFileName = files.Name; dbName = sourceFileName.Split('.')[0]; break; } } if (dbName != "") { DirectoryCopy(SourcePath, DestinationPath, true); dbClass.DBCopy(sqlite_conn, Path.Combine(DestinationPath, sourceFileName), dbName, constants.tbNames); dbClass.CreateSaleTB(sqlite_conn); dbClass.CreateDaySaleTB(sqlite_conn); dbClass.CreateReceiptTB(sqlite_conn); dbClass.CreateCancelOrderTB(sqlite_conn); mainPanelGlobal.Controls.Clear(); MainMenu mainMenu = new MainMenu(); mainMenu.CreateMainMenuScreen(mainFormGlobal, mainPanelGlobal); } else { messageDialog.ShowMenuReadingMessage(); } } catch (Exception ex) { Console.WriteLine(ex); messageDialog.ShowMenuReadingMessage(); } } }
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; }