private void CheckDb(string date) { //check if table allready exist //sqliteConn.Open(); var dtKtra = sqliteConn.DataTable_Sql("SELECT name FROM sqlite_master WHERE type='table' AND name='AgvData_" + date + "'"); if (dtKtra.Rows.Count > 0) { RecordDebug.Print("Table [AgvData_" + date + "] already exist"); RecordDebug.Print("Upload data to table: [AgvData_" + date + "]"); } else { RecordDebug.Print("Create table [AgvData_" + date + "]"); sqliteConn.Execute_NonSQL("CREATE TABLE [AgvData_" + date + "]( " + "[Id] BIGINT PRIMARY KEY," + "[Dept] VARCHAR(50) NULL," + "[Block] VARCHAR(50) NULL," + "[DateTime] DATETIME NULL," + "[Item] VARCHAR(50) NULL," + "[Para1] VARCHAR(50) NULL," + "[Value1] VARCHAR(50) NULL," + "[Para2] VARCHAR(50) NULL," + "[Value2] VARCHAR(50) NULL," + "[Para3] VARCHAR(50) NULL," + "[Value3] VARCHAR(50) NULL)" ); } //sqliteConn.Close(); }
public void CalculateSupplying(string date, string dept, string block, string agv, string time) { RecordDebug.Print("Calcualate supplying detail for: " + agv + " ------- Dept: " + dept + " Block" + block); bool calAbNormal; DateTime startTime, stopTime, startAbnormal, stopAbnormal; Int32 count; string part; int route; string query = "select Dept, Block, DateTime, Item, para1, value1, Value2,Value3 FROM [AgvData_" + date + "] " + "where Dept = '" + dept + "' and Block = '" + block + "' and Item = '" + agv + "' and Para1 = 'Working status' ";// + //time; var dtKtra = sqliteConn.DataTable_Sql(query); if (dtKtra.Rows.Count > 0) { calAbNormal = false; count = 0; while (count < dtKtra.Rows.Count - 3) { if ((dtKtra.Rows[count]["Value1"].ToString() == "SUPPLYING") && (dtKtra.Rows[count + 1]["Value1"].ToString() == "RETURNING") && (dtKtra.Rows[count + 2]["Value1"].ToString() == "FREE")) { if (calAbNormal) { stopAbnormal = DateTime.Parse(dtKtra.Rows[count]["DateTime"].ToString()); calAbNormal = false; } startTime = DateTime.Parse(dtKtra.Rows[count]["DateTime"].ToString()); stopTime = DateTime.Parse(dtKtra.Rows[count + 2]["DateTime"].ToString()); TimeSpan supplyTime = stopTime - startTime; part = dtKtra.Rows[count]["Value3"].ToString(); route = Int16.Parse(dtKtra.Rows[count]["Value2"].ToString()); SupplyDetail _supply = new SupplyDetail(date, dept, block, agv, part, route, startTime.ToString("HH:mm:ss"), stopTime.ToString("HH:mm:ss"), Math.Round(supplyTime.TotalMinutes, 1)); AnalyseDetailByTime(date, dept, block, _supply); listSupplying.Add(_supply); count += 3; } else { if (!calAbNormal) { startAbnormal = DateTime.Parse(dtKtra.Rows[count]["DateTime"].ToString()); calAbNormal = true; } count++; } } } }
private void CalculateThread(object obj) { StructDataToThread d = (StructDataToThread)obj; string date = d._date; string dept = d._dept; string block = d._block; string _StartShipTime = "08:00:00"; string _StartFirstBreakTime = "09:50:00"; string _EndFirstBreakTime = "10:00:00"; string _StartEatTime = "11:40:00"; string _EndEatTime = "12:35:00"; string _StartSecondBreakTime = "14:50:00"; string _EndSecondBreakTime = "14:00:00"; string _EndShipTime = "14:00:00"; DateTime StartShipTime = DateTime.ParseExact(date + " " + _StartShipTime, "yyyyMMdd HH:mm:ss", CultureInfo.InvariantCulture); DateTime StartFirstBreakTime = DateTime.ParseExact(date + " " + _StartFirstBreakTime, "yyyyMMdd HH:mm:ss", CultureInfo.InvariantCulture); DateTime EndFirstBreakTime = DateTime.ParseExact(date + " " + _EndFirstBreakTime, "yyyyMMdd HH:mm:ss", CultureInfo.InvariantCulture); DateTime StartEatTime = DateTime.ParseExact(date + " " + _StartEatTime, "yyyyMMdd HH:mm:ss", CultureInfo.InvariantCulture); DateTime EndEatTime = DateTime.ParseExact(date + " " + _EndEatTime, "yyyyMMdd HH:mm:ss", CultureInfo.InvariantCulture); DateTime StartSecondBreakTime = DateTime.ParseExact(date + " " + _StartSecondBreakTime, "yyyyMMdd HH:mm:ss", CultureInfo.InvariantCulture); DateTime EndSecondBreakTime = DateTime.ParseExact(date + " " + _EndSecondBreakTime, "yyyyMMdd HH:mm:ss", CultureInfo.InvariantCulture); DateTime EndShipTime = DateTime.ParseExact(date + " " + _EndShipTime, "yyyyMMdd HH:mm:ss", CultureInfo.InvariantCulture); string[] normalStt = { "NORMAL", "STOP_BY_CARD", "SAFETY", "NO_CART", "BATTERY_EMPTY", "OUT_OF_LINE", "EMERGENCY", "POLE_ERROR", "FREE" }; //string agv; string time2 = "((cast(datetime as time)>='" + StartShipTime + "' and cast(datetime as time)<='" + StartFirstBreakTime + "') or " + "(cast(datetime as time) >= '" + EndFirstBreakTime + "' and cast(datetime as time) <= '" + StartEatTime + "') or " + "(cast(datetime as time) >= '" + EndEatTime + "' and cast(datetime as time) <= '" + StartSecondBreakTime + "') or " + "(cast(datetime as time) >= '" + EndSecondBreakTime + "' and cast(datetime as time) <= '" + EndShipTime + "')) " + "order by DateTime"; string time = "(cast(datetime as time)>='" + StartShipTime + "' and cast(datetime as time) <= '" + EndShipTime + "') " + "order by DateTime"; string getAgv = "SELECT DISTINCT Item, Dept, Block FROM [AGVData_" + date + "] " + "where Item like 'AGV%' and dept= '" + dept + "' and block = '" + block + "'"; var listAgvs = sqliteConn.DataTable_Sql(getAgv); time = "and(cast(datetime as time) >= '08:00:00 AM' and cast(datetime as time) <= '17:00:00 PM') " + "order by DateTime"; if (listAgvs.Rows.Count > 0) { for (int i = 0; i < listAgvs.Rows.Count; i++) { string agv = listAgvs.Rows[i]["item"].ToString(); analyseData.CalculateSupplying(date, dept, block, agv, time); } } RecordDebug.Print(DateTime.Now.ToString("HH:mm:ss") + " finish calculate " + dept + " " + block); //RecordDebug.stopTime = Environment.TickCount; //RecordDebug.Print("Total time to calculate: " + ((RecordDebug.stopTime - RecordDebug.startTime) / 1000).ToString()); }
private void OpenAndCalculate(object obje) { string date = ((StructDataToThread)obje)._date; string dept = ((StructDataToThread)obje)._dept; string block = ((StructDataToThread)obje)._block; string path = ((StructDataToThread)obje)._filePath; RawList = new List <RawData>(); List <string> s = new List <string>(); s = File.ReadLines(path).ToList(); setMaximum(s.Count); //CheckDb(date); RecordDebug.Print("Start copy at " + DateTime.Now.ToString("HH:mm:ss")); int startTime = Environment.TickCount; FileStream fStream = new FileStream(path, FileMode.Open); StreamReader sReader = new StreamReader(fStream); int count = 0; while (!sReader.EndOfStream) { string line = sReader.ReadLine(); string[] values = line.Split(','); RawData r = new RawData { Dept = dept, Block = block, DateTime = DateTime.Parse(values[0]), Item = values[1], Para1 = values[2], Value1 = values[3], Para2 = values[4], Value2 = values[5], Para3 = values[6], Value3 = values[7] }; RawList.Add(r); count++; //showProgresss(count); } fStream.Close(); sReader.Close(); int stopTime = Environment.TickCount; float copyTime = (stopTime - startTime) / 1000; RecordDebug.Print("Copy time: " + copyTime.ToString()); RecordDebug.Print("Start calculate at " + DateTime.Now.ToString("HH:mm:ss")); Calculate(date, dept, block); }
private void Calculate(string date, string dept, string block) { List <string> listAGV = RawList .Select(a => a.Item) .Where(a => a.StartsWith("AGV")) .GroupBy(a => a) .Select(a => a.First()) .ToList(); sqliteConn.StartInsert(); for (int i = 0; i < listAGV.Count; i++) { RecordDebug.Print("Calculating supplying detail for " + listAGV[i]); CalculateSupplyTime(date, dept, block, listAGV[i]); } sqliteConn.EndInsert(); RecordDebug.Print("Finish calculate supplying detail at " + DateTime.Now.ToString()); }
public MainForm() { InitializeComponent(); RecordDebug.SetLoggerForm(ucLogger); sqliteConn.Open(); }
private void MainForm_Load(object sender, EventArgs e) { RecordDebug.SetLoggerForm(ucLogger); }
private void OpenAndCalculate(object obje) { string date = ((StructDataToThread)obje)._date; string dept = ((StructDataToThread)obje)._dept; string block = ((StructDataToThread)obje)._block; string path = ((StructDataToThread)obje)._filePath; List <string> s = new List <string>(); s = File.ReadLines(path).ToList(); setMaximum(s.Count); CheckDb(date); RecordDebug.Print("Start copy at " + DateTime.Now.ToString("HH:mm:ss")); int startTime = Environment.TickCount; FileStream fStream = new FileStream(path, FileMode.Open); StreamReader sReader = new StreamReader(fStream); int count = 0; //sqliteConn.Open(); sqliteConn.StartInsert(); while (!sReader.EndOfStream) { string line = sReader.ReadLine(); string[] values = line.Split(','); sqliteConn.Execute_NonSQL("insert into [AgvData_" + date + "](Dept,Block,DateTime,Item,Para1,Value1,Para2,Value2,Para3,Value3)" + "values('" + dept + "','" + block + "','" + values[0] + "','" + values[1] + "','" + values[2] + "','" + values[3] + "','" + values[4] + "','" + values[5] + "','" + values[6] + "','" + values[7] + "')"); count++; //showProgresss(count); } fStream.Close(); sReader.Close(); //sqliteConn.EndInsert(); //sqliteConn.Close(); int stopTime = Environment.TickCount; float copyTime = (stopTime - startTime) / 1000; RecordDebug.Print("Copy time: " + copyTime.ToString()); RecordDebug.Print("Start calculate at " + DateTime.Now.ToString("HH:mm:ss")); string getDept = "SELECT DISTINCT Dept FROM [AGVData_" + date + "]"; var listDept = sqliteConn.DataTable_Sql(getDept); if (listDept.Rows.Count > 0) { for (int deptNum = 0; deptNum < listDept.Rows.Count; deptNum++) { dept = listDept.Rows[deptNum]["Dept"].ToString(); string getBlock = "SELECT DISTINCT Block FROM [AGVData_" + date + "] " + "where Dept= '" + listDept.Rows[deptNum]["Dept"].ToString() + "'"; var listBlock = sqliteConn.DataTable_Sql(getBlock); if (listBlock.Rows.Count > 0) { for (int blockNum = 0; blockNum < listBlock.Rows.Count; blockNum++) { block = listBlock.Rows[blockNum]["Block"].ToString(); //CalculateThread(new StructDataToThread() { _date = date, _dept = dept, _block = block }); Thread calculateThread = new Thread((obj) => { StructDataToThread st = (StructDataToThread)obj; CalculateThread(st); }); calculateThread.Name = date + " " + dept + " " + block; calculateThread.Start(new StructDataToThread() { _date = date, _dept = dept, _block = block }); block = ""; } } dept = ""; } } }