public void dml001() { string sql = @"SELECT EMPNUM,HOURS FROM WORKS WHERE PNUM='P2' ORDER BY EMPNUM DESC;"; System.Collections.Generic.List <physic.Row> stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual(4, stmtResult.Count); Assert.AreEqual(stmtResult[3][0].ToString(), "E1"); sql = @" SELECT EMPNUM,HOURS FROM WORKS WHERE PNUM='P2' ORDER BY 2 ASC;"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual(4, stmtResult.Count); Assert.AreEqual(stmtResult[3][1].ToString(), "80"); sql = @" SELECT EMPNUM,HOURS FROM WORKS WHERE PNUM = 'P2' ORDER BY 2 DESC,EMPNUM DESC;"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual(4, stmtResult.Count); Assert.AreEqual(stmtResult[3][0].ToString(), "E1"); }
public void dml001() { var sql = @"SELECT EMPNUM,HOURS FROM WORKS WHERE PNUM='P2' ORDER BY EMPNUM DESC;"; var stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual(4, stmtResult.Count); Assert.AreEqual(stmtResult[3][0].ToString(), "E1"); sql = @" SELECT EMPNUM,HOURS FROM WORKS WHERE PNUM='P2' ORDER BY 2 ASC;"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual(4, stmtResult.Count); Assert.AreEqual(stmtResult[3][1].ToString(), "80"); sql = @" SELECT EMPNUM,HOURS FROM WORKS WHERE PNUM = 'P2' ORDER BY 2 DESC,EMPNUM DESC;"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual(4, stmtResult.Count); Assert.AreEqual(stmtResult[3][0].ToString(), "E1"); }
public void dml013() { var sql = @" SELECT SUM(HOURS) FROM WORKS WHERE PNUM = 'P2';"; var stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual(1, stmtResult.Count); Assert.AreEqual(stmtResult[0].ToString(), "140"); sql = @" SELECT SUM(HOURS)+10 FROM WORKS WHERE PNUM = 'P2';"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual(1, stmtResult.Count); Assert.AreEqual(stmtResult[0][0].ToString(), "150"); sql = @" SELECT EMPNUM FROM STAFF WHERE GRADE = (SELECT MAX(GRADE) FROM STAFF) ORDER BY EMPNUM;"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual(2, stmtResult.Count); Assert.AreEqual("E3,E5", string.Join(",", stmtResult)); }
public void SimulateIncomingTUs(string place, string skuid, string batch, double qty) { using (var dc = new WMSContext()) { Place p = dc.Places.FirstOrDefault(prop => prop.PlaceID == place); if (p != null) { TU tu = dc.TUs.FirstOrDefault(prop => prop.TU_ID == p.TU_ID); if (tu == null) { dc.TUs.Add(tu = new TU { Batch = batch, ExpDate = DateTime.Now.Date, ProdDate = DateTime.Now.Date, Qty = qty, TU_ID = p.TU_ID, SKU_ID = skuid }); dc.SaveChanges(); Log.AddLog(Log.SeverityEnum.Event, nameof(SimulateIncomingTUs), $"Simulate new TU {tu.ToString()}"); } } } }
/// <summary> /// 为导入EXcel的表格设置自适应边框 /// </summary> /// <param name="PCIoldFinDT"></param> /// <param name="PCi_Sheet"></param> /// <param name="begincell"></param> public static void Frame(System.Data.DataTable PCIoldFinDT, _Worksheet PCi_Sheet, string begincell) { int moveothcn = PCIoldFinDT.Columns.Count - 1; string moveoth1 = MYTUW.TUW.Move_excel_active_cell(1, begincell, moveothcn); int moveothro = PCIoldFinDT.Rows.Count; string moveoth2 = MYTUW.TUW.Move_excel_active_cell(0, moveoth1, moveothro); TU.set_Table_Format(PCi_Sheet, begincell, moveoth2); }
public void dml023() { var sql = @" SELECT PNUM FROM PROJ WHERE PROJ.CITY = (SELECT STAFF.CITY FROM STAFF WHERE EMPNUM = 'E1');"; TU.ExecuteSQL(sql, "P1;P4;P6"); }
public override string BuildXml() { XElement el0 = null; LoadSchema(); XDocument XDocument = new XDocument(new XDeclaration("1.0", "UTF-8", "yes"), el0 = new XElement("Belgeler")); XNamespace ns = XDocument.Root.Name.Namespace; // belgeler el0.Add(new XElement("Baslik")); el0.Add(new XElement("Detaylar")); // baslik el0.Element(ns + "Baslik").Add(new XElement("BelgeKodu", XmlConvert.ToString(DocumentID))); el0.Element(ns + "Baslik").Add(new XElement("Tesis", "Aksaray")); using (var dc = new WMSContext()) { foreach (var cmd in Commands) { if (cmd.Order_ID.HasValue) { Order order = dc.Orders.Find(cmd.Order_ID.Value); SKU_ID skuid = dc.SKU_IDs.Find(order.SKU_ID); TU tu = dc.TUs.FirstOrDefault(prop => prop.TU_ID == cmd.TU_ID); // Detay el0.Element(ns + "Detaylar").Add(new XElement("Detay")); (el0.Element(ns + "Detaylar").LastNode as XElement).Add(new XElement("BelgeKodu", XmlConvert.ToString(order.ERP_ID.HasValue ? order.SubOrderERPID: 0))); // (el0.Element(ns + "Detaylar").LastNode as XElement).Add(new XElement("RefBelgeDetayNo")); (el0.Element(ns + "Detaylar").LastNode as XElement).Add(new XElement("UrunKod", tu.SKU_ID)); (el0.Element(ns + "Detaylar").LastNode as XElement).Add(new XElement("Miktar", XmlConvert.ToString(tu.Qty))); (el0.Element(ns + "Detaylar").LastNode as XElement).Add(new XElement("Birim", skuid.Unit)); // (el0.Element(ns + "Detaylar").LastNode as XElement).Add(new XElement("NetAgirLik")); // (el0.Element(ns + "Detaylar").LastNode as XElement).Add(new XElement("AgirlikBirimi")); (el0.Element(ns + "Detaylar").LastNode as XElement).Add(new XElement("KaynakBatchNo", tu.Batch)); // (el0.Element(ns + "Detaylar").LastNode as XElement).Add(new XElement("HedefBatchNo"), tu.Batch); // (el0.Element(ns + "Detaylar").LastNode as XElement).Add(new XElement("SeriNo")); // (el0.Element(ns + "Detaylar").LastNode as XElement).Add(new XElement("KaynakLokasyon")); (el0.Element(ns + "Detaylar").LastNode as XElement).Add(new XElement("HedefLokasyon", order.Destination)); // (el0.Element(ns + "Detaylar").LastNode as XElement).Add(new XElement("KaynakStatus")); // (el0.Element(ns + "Detaylar").LastNode as XElement).Add(new XElement("HedefStatu")); (el0.Element(ns + "Detaylar").LastNode as XElement).Add(new XElement("PaletNo", $"{cmd.TU_ID:d9}")); // (el0.Element(ns + "Detaylar").LastNode as XElement).Add(new XElement("Po")); // (el0.Element(ns + "Detaylar").LastNode as XElement).Add(new XElement("PoLine")); // (el0.Element(ns + "Detaylar").LastNode as XElement).Add(new XElement("SKT")); // (el0.Element(ns + "Detaylar").LastNode as XElement).Add(new XElement("URT")); } } } return(XDocument.ToString()); }
/// <summary> /// Find identical TU (skuid,batch,qty) with nearest prod date on level 2 with free level 1 /// </summary> /// <param name="cmd"></param> /// <returns></returns> public static string FindBrotherOnDepth2(this DTOCommand cmd) { List <string> reck = new List <string> { "W:11", "W:12", "W:21", "W:22" }; using (var dc = new WMSContext()) { TU tu = dc.TUs.FirstOrDefault(prop => prop.TU_ID == cmd.TU_ID); string brother = dc.Places .Where(prop => reck.Any(p => prop.PlaceID.StartsWith(p)) && prop.PlaceID.EndsWith("2")) .Where(prop => prop.FK_PlaceID.Status == 0) .Where(prop => !dc.Places.Any(p => p.PlaceID == prop.PlaceID.Substring(0, 10) + ":1")) .Where(prop => !dc.Commands.Any(p => (p.Source == prop.PlaceID && p.Status < Command.CommandStatus.Canceled) || (p.Target == prop.PlaceID.Substring(0, 10) + ":1" && p.Status < Command.CommandStatus.Canceled))) .Select(prop => new { Place = prop.PlaceID, TU = prop.FK_TU_ID.FK_TU.FirstOrDefault() }) .Where(prop => prop.TU.Batch == tu.Batch && prop.TU.SKU_ID == tu.SKU_ID && prop.TU.Qty == tu.Qty) .Union( dc.Commands .Where(prop => reck.Any(p => prop.Target.StartsWith(p)) && prop.Target.EndsWith("2") && prop.Status < Command.CommandStatus.Canceled) .Where(prop => !dc.Commands.Any(p => p.Target == prop.Target.Substring(0, 10) + ":1" && p.Status < Command.CommandStatus.Canceled)) .Where(prop => !dc.Places.Any(p => p.PlaceID == prop.Target.Substring(0, 10) + ":1")) .Where(prop => dc.PlaceIds.Any(p => p.ID == prop.Target.Substring(0, 10) + ":1" && p.Status == 0)) .Select(prop => new { Place = prop.Target, TU = prop.FK_TU_ID.FK_TU.FirstOrDefault() }) .Where(prop => prop.TU.Batch == tu.Batch && prop.TU.SKU_ID == tu.SKU_ID && prop.TU.Qty == tu.Qty) ) .Where(prop => prop.Place.EndsWith("2")) .OrderBy(prop => DbFunctions.DiffHours(prop.TU.ExpDate, tu.ExpDate)) // add order by production date .Select(prop => prop.Place) .FirstOrDefault(); return(brother); } }
public void CreateVTABLE() { string sql = "CREATE TABLE VTABLE (COL1 INTEGER, COL2 INTEGER, COL3 INTEGER, COL4 INTEGER, COL5 DECIMAL(7,2));"; System.Collections.Generic.List <physic.Row> stmtResult = TU.ExecuteSQL(sql); Assert.IsNull(stmtResult); Assert.AreEqual("", TU.error_); sql = "INSERT INTO VTABLE VALUES(10,+20,30,40,10.50);"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "INSERT INTO VTABLE VALUES(0,1,2,3,4.25);"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "INSERT INTO VTABLE VALUES(100,200,300,400,500.01);"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "INSERT INTO VTABLE VALUES(1000,-2000,3000,NULL,4000.00);"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); }
public void CreateVTABLE() { var sql = "CREATE TABLE VTABLE (COL1 INTEGER, COL2 INTEGER, COL3 INTEGER, COL4 INTEGER, COL5 DECIMAL(7,2));"; var stmtResult = TU.ExecuteSQL(sql); Assert.IsNull(stmtResult); Assert.AreEqual("", TU.error_); sql = "INSERT INTO VTABLE VALUES(10,+20,30,40,10.50);"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "INSERT INTO VTABLE VALUES(0,1,2,3,4.25);"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "INSERT INTO VTABLE VALUES(100,200,300,400,500.01);"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "INSERT INTO VTABLE VALUES(1000,-2000,3000,NULL,4000.00);"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); }
public void dml073() { var sql = @" SELECT AVG(HOURS), MIN(HOURS) FROM STAFF, WORKS WHERE STAFF.EMPNUM = 'E2' AND STAFF.EMPNUM = WORKS.EMPNUM;"; var stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual(1, stmtResult.Count); Assert.AreEqual(stmtResult[0][0].ToString(), "60"); Assert.AreEqual(stmtResult[0][1].ToString(), "40"); sql = @" SELECT STAFF.EMPNUM, AVG(HOURS), MIN(HOURS) FROM STAFF, WORKS WHERE STAFF.EMPNUM IN ('E1','E4','E3') AND STAFF.EMPNUM = WORKS.EMPNUM GROUP BY STAFF.EMPNUM HAVING COUNT(*) > 1 ORDER BY STAFF.EMPNUM;"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual(2, stmtResult.Count); Assert.AreEqual(stmtResult[0][0].ToString(), "E1"); decimal DecNum = Convert.ToDecimal(stmtResult[0][1].ToString()); Assert.IsTrue(DecNum >= 30 && DecNum <= 31); Assert.AreEqual(stmtResult[0][2].ToString(), "12"); Assert.AreEqual(stmtResult[1][0].ToString(), "E4"); DecNum = Convert.ToDecimal(stmtResult[1][1].ToString()); Assert.IsTrue(DecNum >= 46 && DecNum <= 47); Assert.AreEqual(stmtResult[1][2].ToString(), "20"); // TEST:0418. Removed DISTINCT from COUNT, with DISTINCT // count shall be 3, without it 12. sql = @" SELECT AVG(T1.COL4), AVG(T1.COL4 + T2.COL4), SUM(T2.COL4), COUNT(T1.COL4) FROM VTABLE T1, VTABLE T2;"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual(1, stmtResult.Count); DecNum = Convert.ToDecimal(stmtResult[0][0].ToString()); Assert.IsTrue(DecNum >= 147 && DecNum <= 148); DecNum = Convert.ToDecimal(stmtResult[0][1].ToString()); Assert.IsTrue(DecNum >= 295 && DecNum <= 296); Assert.AreEqual(stmtResult[0][2].ToString(), "1772"); Assert.AreEqual(stmtResult[0][3].ToString(), "12"); #if false // dml075: TEST:0434. Just one interesting test, not // adding another method. // ERROR: "WHERE condition must be a blooean expression and no aggregation is allowed" sql = @" SELECT PNUM, SUM(HOURS) FROM WORKS GROUP BY PNUM HAVING EXISTS (SELECT PNAME FROM PROJ WHERE PROJ.PNUM = WORKS.PNUM AND SUM(WORKS.HOURS) > PROJ.BUDGET / 200);"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual(2, stmtResult.Count); String Pnum1 = stmtResult[0][0].ToString(); String Pnum2 = stmtResult[1][0].ToString(); DecNum = Convert.ToDecimal(stmtResult[1][0].ToString()); decimal DecNum2 = Convert.ToDecimal(stmtResult[1][1].ToString()); /* * -- PASS:0434 If 2 rows selected with values (in any order):? * -- PASS:0434 PNUM = 'P1', SUM(HOURS) = 80? * -- PASS:0434 PNUM = 'P5', SUM(HOURS) = 92? */ Assert.IsTrue((Pnum1 == "P1" && DecNum == 80 && Pnum2 == "P5" && DecNum2 == 92) || (Pnum1 == "P5" && DecNum == 92 && Pnum2 == "P1" && DecNum2 == 80)); #endif // dml090 #if false /* BUG */ sql = @" SELECT MIN(PNAME) FROM PROJ, WORKS, STAFF WHERE PROJ.PNUM = WORKS.PNUM AND WORKS.EMPNUM = STAFF.EMPNUM AND BUDGET - GRADE * HOURS * 100 IN (-4400, -1000, 4000);"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual(1, stmtResult.Count); Assert.AreEqual(stmtResult[0][0].ToString(), "CALM"); #endif sql = @" SELECT CITY, COUNT(*) FROM PROJ GROUP BY CITY HAVING (MAX(BUDGET) - MIN(BUDGET)) / 2 IN (2, 20000, 10000) ORDER BY CITY DESC;"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual(2, stmtResult.Count); Assert.AreEqual(stmtResult[0][0].ToString(), "Vienna"); Assert.AreEqual(stmtResult[0][1].ToString(), "2"); Assert.AreEqual(stmtResult[1][0].ToString(), "Deale"); Assert.AreEqual(stmtResult[1][1].ToString(), "3"); sql = @" SELECT COUNT(*) FROM PROJ WHERE 24 * 1000 BETWEEN BUDGET - 5000 AND 50000 / 1.7;"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual(stmtResult[0][0].ToString(), "3"); #if false /* * BUG: Expected one row: 'IRM' * Actual three rows: 'SDP'; 'SDP'; 'PAYR' */ sql = @" SELECT PNAME FROM PROJ WHERE 'Tampa' NOT BETWEEN CITY AND 'Vienna' AND PNUM > 'P2';"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual(1, stmtResult.Count); Assert.AreEqual(stmtResult[0][0].ToString(), "IRM"); #endif sql = @" SELECT CITY, COUNT(*) FROM PROJ GROUP BY CITY HAVING 50000 + 2 BETWEEN 33000 AND SUM(BUDGET) - 20;"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual(1, stmtResult.Count); Assert.AreEqual(stmtResult[0][0].ToString(), "Deale"); Assert.AreEqual(stmtResult[0][1].ToString(), "3"); // dml158 #if false /* BUG */ sql = @" SELECT EMPNUM, SUM (HOURS) FROM WORKS OWORKS GROUP BY EMPNUM HAVING EMPNUM IN ( SELECT WORKS.EMPNUM FROM WORKS JOIN STAFF ON WORKS.EMPNUM = STAFF.EMPNUM AND HOURS < SUM (OWORKS.HOURS) / 3 AND GRADE > 10) ORDER BY EMPNUM;"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual(2, stmtResult.Count); Assert.AreEqual(stmtResult[0][0].ToString(), "E1"); Assert.AreEqual(stmtResult[0][1].ToString(), "184"); Assert.AreEqual(stmtResult[1][0].ToString(), "E4"); Assert.AreEqual(stmtResult[0][1].ToString(), "140"); sql = @" SELECT EMPNUM, SUM (HOURS) FROM WORKS OWORKS GROUP BY EMPNUM HAVING EMPNUM IN ( SELECT WORKS.EMPNUM FROM WORKS JOIN STAFF ON WORKS.EMPNUM = STAFF.EMPNUM AND HOURS >= 10 + AVG (OWORKS.HOURS) AND CITY = 'Deale') ORDER BY EMPNUM;"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual(2, stmtResult.Count); Assert.AreEqual(stmtResult[0][0].ToString(), "E1"); Assert.AreEqual(stmtResult[0][1].ToString(), "184"); Assert.AreEqual(stmtResult[1][0].ToString(), "E4"); Assert.AreEqual(stmtResult[0][1].ToString(), "140"); #endif // Try them in FROM // -- TEST:0004 SELECT with UNION, ORDER BY integer DESC! // -- PASS:0004 If 5 rows selected and last EMPNUM = 'E1'? sql = @" SELECT WEMPNUM FROM ( SELECT WORKS.EMPNUM FROM WORKS WHERE WORKS.PNUM = 'P2' UNION SELECT STAFF.EMPNUM FROM STAFF WHERE STAFF.GRADE=13) WEMP(WEMPNUM) ORDER BY 1 DESC "; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual(5, stmtResult.Count); Assert.AreEqual(stmtResult[4][0].ToString(), "E1"); // -- TEST:0005 SELECT with UNION ALL! // -- PASS:0005 If 6 rows selected? sql = @" SELECT WEMPNUM FROM ( SELECT WORKS.EMPNUM FROM WORKS WHERE WORKS.PNUM = 'P2' UNION ALL SELECT STAFF.EMPNUM FROM STAFF WHERE STAFF.GRADE = 13 ) WEMP(WEMPNUM); "; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual(6, stmtResult.Count); #if false // -- TEST:0158 SELECT with UNION and NOT EXISTS subquery! // -- PASS:0158 If 21 rows selected? /* * EXCEPTION: * PhysicMarkJoin.Exec(Action`1 callback) line 490 * PhysicProfiling.Exec(Action`1 callback) line 349 * PhysicFilter.Exec(Action`1 callback) line 1596 * PhysicProfiling.Exec(Action`1 callback) line 349 */ sql = @" SELECT EMPNAME,PNUM,HOURS FROM STAFF,WORKS WHERE STAFF.EMPNUM = WORKS.EMPNUM UNION SELECT EMPNAME,PNUM,HOURS FROM STAFF,WORKS WHERE NOT EXISTS (SELECT HOURS FROM WORKS WHERE STAFF.EMPNUM = WORKS.EMPNUM); "; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual(21, stmtResult.Count); // Assert.AreEqual failed. Expected:<>. Actual:<select_core->select_stmt->sql_stmt->sql_stmt_list->parse : 6|12|[@102,141:141='(',<6>,6:12]|extraneous input '(' expecting {K_SELECT, K_VALUES}>. // -- TEST:0160 SELECT with parenthesized UNION, UNION ALL! // -- PASS:0160 If 14 rows selected? sql = @" SELECT PNUM,EMPNUM,HOURS FROM WORKS WHERE HOURS=12 UNION ALL (SELECT PNUM,EMPNUM,HOURS FROM WORKS UNION SELECT PNUM,EMPNUM,HOURS FROM WORKS WHERE HOURS=80) ORDER BY 2,1; "; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual(14, stmtResult.Count); // AGGREGATES on strings not supported. sql = @"SELECT COLUMN_1, COLUMN_2, COLUMN_3 FROM ( SELECT PNUM, BUDGET, CITY FROM PROJ OUTER_REF WHERE BUDGET >= (SELECT AVG(BUDGET) FROM PROJ INNER_REF WHERE OUTER_REF.CITY = INNER_REF.CITY) UNION SELECT 'MAX', MAX(BUDGET), MIN(CITY) FROM PROJ WHERE CITY > 'Deale') ABOVE_AVERAGE (COLUMN_1, COLUMN_2, COLUMN_3) ORDER BY COLUMN_1"; stmtResult = TU.ExecuteSQL(sql); // -- PASS:0599 If 4 rows selected with ordered rows and column values: ? // -- PASS:0599 MAX 30000 Tampa ? // -- PASS:0599 P2 30000 Vienna ? // -- PASS:0599 P3 30000 Tampa ? // -- PASS:0599 P6 50000 Deale ? #endif sql = "select empnum, sum(hours) from (select empnum, hours from works group by hours, empnum) workweek(empnum, hours) where hours > 20 group by empnum having empnum = 'E1';"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual(1, stmtResult.Count); Assert.AreEqual(stmtResult[0][0].ToString(), "E1"); Assert.AreEqual(stmtResult[0][1].ToString(), "120"); }
public void dml059() { var sql = "INSERT INTO VTABLE VALUES(10,11,12,13,15);"; TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "INSERT INTO VTABLE VALUES(100,111,1112,113,115);"; TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = @" SELECT COL1, MAX(COL2 + COL3), MIN(COL3 - COL2) FROM VTABLE GROUP BY COL1 ORDER BY COL1;"; TU.ExecuteSQL(sql, "0,3,1;10,50,1;100,1223,100;1000,1000,5000"); sql = "DROP TABLE VTABLE;"; TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); CreateVTABLE(); sql = "INSERT INTO VTABLE VALUES (10,11,12,13,15);"; TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "INSERT INTO VTABLE VALUES (100,111,1112,113,115);"; TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = @" SELECT COL1,SUM(2 * COL2 * COL3) FROM VTABLE GROUP BY COL1 HAVING SUM(COL2 * COL3) > 2000 OR SUM(COL2 * COL3) < -2000 ORDER BY COL1;"; TU.ExecuteSQL(sql, "100,366864;1000,-12000000"); sql = "DROP TABLE VTABLE"; TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); CreateVTABLE(); sql = "INSERT INTO VTABLE VALUES(10,11,12,13,15);"; TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "INSERT INTO VTABLE VALUES(100,111,1112,113,115);"; TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = @" SELECT COL1, MAX(COL2) FROM VTABLE GROUP BY COL1 HAVING EXISTS (SELECT * FROM STAFF WHERE EMPNUM = 'E1') AND MAX(COL2) BETWEEN 10 AND 90 ORDER BY COL1;"; var stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual(1, stmtResult.Count); Assert.AreEqual(stmtResult[0][0].ToString(), "10"); Assert.AreEqual(stmtResult[0][1].ToString(), "20"); sql = "DROP TABLE VTABLE"; TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); CreateVTABLE(); sql = @" SELECT SUM(COL1) FROM VTABLE WHERE 10 + COL1 > COL2 HAVING MAX(COL1) > 100;"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual(1, stmtResult.Count); Assert.AreEqual(stmtResult[0][0].ToString(), "1000"); sql = @" SELECT SUM(COL1) FROM VTABLE WHERE 1000 + COL1 >= COL2 HAVING MAX(COL1) > 100;"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual(1, stmtResult.Count); Assert.AreEqual(stmtResult[0][0].ToString(), "1110"); }
public void CreateBaseTables() { string sql = "CREATE TABLE STAFF (EMPNUM CHAR(3) NOT NULL UNIQUE, EMPNAME CHAR(20), GRADE DECIMAL(4), CITY CHAR(15));"; var stmtResult = TU.ExecuteSQL(sql); Assert.IsNull(stmtResult); Assert.AreEqual("", TU.error_); sql = "CREATE TABLE PROJ (PNUM CHAR(3) NOT NULL UNIQUE, PNAME CHAR(20), PTYPE CHAR(6), BUDGET DECIMAL(9), CITY CHAR(15));"; stmtResult = TU.ExecuteSQL(sql); Assert.IsNull(stmtResult); Assert.AreEqual("", TU.error_); sql = "CREATE TABLE WORKS (EMPNUM CHAR(3) NOT NULL, PNUM CHAR(3) NOT NULL, HOURS DECIMAL(5), UNIQUE(EMPNUM,PNUM));"; stmtResult = TU.ExecuteSQL(sql); Assert.IsNull(stmtResult); Assert.AreEqual("", TU.error_); sql = "INSERT INTO STAFF VALUES ('E1','Alice',12,'Deale');"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "INSERT INTO STAFF VALUES ('E2','Betty',10,'Vienna');"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "INSERT INTO STAFF VALUES ('E3','Carmen',13,'Vienna');"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "INSERT INTO STAFF VALUES ('E4','Don',12,'Deale');"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "INSERT INTO STAFF VALUES ('E5','Ed',13,'Akron');"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "INSERT INTO PROJ VALUES ('P1','MXSS','Design',10000,'Deale');"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "INSERT INTO PROJ VALUES ('P2','CALM','Code',30000,'Vienna');"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "INSERT INTO PROJ VALUES ('P3','SDP','Test',30000,'Tampa');"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "INSERT INTO PROJ VALUES ('P4','SDP','Design',20000,'Deale');"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "INSERT INTO PROJ VALUES ('P5','IRM','Test',10000,'Vienna');"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "INSERT INTO PROJ VALUES ('P6','PAYR','Design',50000,'Deale');"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "INSERT INTO WORKS VALUES ('E1','P1',40);"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "INSERT INTO WORKS VALUES ('E1','P2',20);"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "INSERT INTO WORKS VALUES ('E1','P3',80);"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "INSERT INTO WORKS VALUES ('E1','P4',20);"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "INSERT INTO WORKS VALUES ('E1','P5',12);"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "INSERT INTO WORKS VALUES ('E1','P6',12);"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "INSERT INTO WORKS VALUES ('E2','P1',40);"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "INSERT INTO WORKS VALUES ('E2','P2',80);"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "INSERT INTO WORKS VALUES ('E3','P2',20);"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "INSERT INTO WORKS VALUES ('E4','P2',20);"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "INSERT INTO WORKS VALUES ('E4','P4',40);"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "INSERT INTO WORKS VALUES ('E4','P5',80);"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); /* * this table gets new rows inserted and deleted, which we don't * support. So, it gets created and populated seperately. */ CreateVTABLE(); // this table gets new rows inserted and deleted }
public void dml022() { var sql = @" SELECT EMPNUM FROM STAFF WHERE GRADE < (SELECT MAX(GRADE) FROM STAFF);"; TU.ExecuteSQL(sql, "E1;E2;E4"); sql = @" SELECT * FROM STAFF WHERE GRADE <= (SELECT AVG(GRADE)-1 FROM STAFF);"; var stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual(1, stmtResult.Count); Assert.AreEqual(stmtResult[0][0].ToString(), "E2"); Assert.AreEqual(stmtResult[0][1].ToString(), "Betty"); sql = @" SELECT EMPNAME FROM STAFF WHERE EMPNUM IN (SELECT EMPNUM FROM WORKS WHERE PNUM = 'P2') ORDER BY EMPNAME;"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual(4, stmtResult.Count); Assert.AreEqual(stmtResult[0][0].ToString(), "Alice"); sql = @" SELECT EMPNAME FROM STAFF WHERE EMPNUM IN (SELECT EMPNUM FROM WORKS WHERE PNUM IN (SELECT PNUM FROM PROJ WHERE PTYPE = 'Design'));"; TU.ExecuteSQL(sql, "Alice;Betty;Don"); sql = @" SELECT EMPNUM, EMPNAME FROM STAFF WHERE EMPNUM IN (SELECT EMPNUM FROM WORKS WHERE PNUM IN (SELECT PNUM FROM PROJ WHERE PTYPE IN (SELECT PTYPE FROM PROJ WHERE PNUM IN (SELECT PNUM FROM WORKS WHERE EMPNUM IN (SELECT EMPNUM FROM WORKS WHERE PNUM IN (SELECT PNUM FROM PROJ WHERE PTYPE = 'Design')))))) ORDER BY EMPNUM;"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual(4, stmtResult.Count); Assert.AreEqual(stmtResult[0][0].ToString(), "E1"); #if false /* * BUG: should return two rows but returns 12 rows */ sql = @" SELECT DISTINCT EMPNUM FROM WORKS WORKSX WHERE NOT EXISTS (SELECT * FROM WORKS WORKSY WHERE EMPNUM = 'E2' AND NOT EXISTS (SELECT * FROM WORKS WORKSZ WHERE WORKSZ.EMPNUM = WORKSX.EMPNUM AND WORKSZ.PNUM = WORKSY.PNUM));"; TU.ExecuteSQL(sql, "E1;E2"); #endif }
public void dml018() { var sql = @" SELECT PNUM FROM WORKS WHERE PNUM > 'P1' GROUP BY PNUM HAVING COUNT(*) > 1;"; TU.ExecuteSQL(sql, "P2;P4;P5"); sql = @" SELECT PNUM FROM WORKS GROUP BY PNUM HAVING COUNT(*) > 2;"; var stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual(1, stmtResult.Count); Assert.AreEqual(stmtResult[0][0].ToString(), "P2"); sql = @" SELECT EMPNUM, PNUM, HOURS FROM WORKS GROUP BY PNUM, EMPNUM, HOURS HAVING MIN(HOURS) > 12 AND MAX(HOURS) < 80;"; TU.ExecuteSQL(sql, "E1,P1,40;E1,P2,20;E1,P4,20;E2,P1,40;E3,P2,20;E4,P2,20;E4,P4,40"); sql = @" SELECT WORKS.PNUM FROM WORKS GROUP BY WORKS.PNUM HAVING WORKS.PNUM IN (SELECT PROJ.PNUM FROM PROJ GROUP BY PROJ.PNUM HAVING SUM(PROJ.BUDGET) > 25000);"; TU.ExecuteSQL(sql, "P2;P3;P6"); /* * implement string compare operators. */ #if false /* * Aggregate on strings is not implemented. Throws runtime exception. * */ sql = @" SELECT SUM(HOURS) FROM WORKS HAVING MIN(PNUM) > 'P0';"; stmtResult = TU.ExecuteSQL(sql); Assert.IsTrue(TU.error_.Contains("Operator '>' cannot be applied to")); Assert.AreEqual(1, stmtResult.Count); Assert.AreEqual(stmtResult[0][0].ToString(), "464"); #endif sql = @"SELECT PNUM FROM WORKS WHERE PNUM > 'P1' GROUP BY PNUM HAVING COUNT(*) > 1;"; TU.ExecuteSQL(sql, "P2;P4;P5"); }
/// <summary> /// Creates an instance of an item in a <see cref="BinaryTree<T, U>"/>. /// </summary> /// <param name="value">Value</param> /// <param name="id">Identifier for the value</param> public ItemValue(T value, TU id) { Value = value; Id = id; }
/// <summary> /// Converts the left and right values to optional pair. /// If one of the value is null, returns an empty optional. /// </summary> /// <typeparam name="T">The Type of the value.</typeparam> /// <typeparam name="TU">The type of the right value.</typeparam> /// <param name="left">The left value to act on.</param> /// <param name="right">The right value to act on.</param> public static Optional <(T Left, TU Right)> And <T, TU>(this T left, TU right) => left.AsOptional().And(right);
public void dml073() { var sql = @" SELECT AVG(HOURS), MIN(HOURS) FROM STAFF, WORKS WHERE STAFF.EMPNUM = 'E2' AND STAFF.EMPNUM = WORKS.EMPNUM;"; var stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); Assert.AreEqual(1, stmtResult.Count); Assert.AreEqual(stmtResult[0][0].ToString(), "60"); Assert.AreEqual(stmtResult[0][1].ToString(), "40"); sql = @" SELECT STAFF.EMPNUM, AVG(HOURS), MIN(HOURS) FROM STAFF, WORKS WHERE STAFF.EMPNUM IN ('E1','E4','E3') AND STAFF.EMPNUM = WORKS.EMPNUM GROUP BY STAFF.EMPNUM HAVING COUNT(*) > 1 ORDER BY STAFF.EMPNUM;"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); Assert.AreEqual(2, stmtResult.Count); Assert.AreEqual(stmtResult[0][0].ToString(), "E1"); decimal DecNum = Convert.ToDecimal(stmtResult[0][1].ToString()); Assert.IsTrue(DecNum >= 30 && DecNum <= 31); Assert.AreEqual(stmtResult[0][2].ToString(), "12"); Assert.AreEqual(stmtResult[1][0].ToString(), "E4"); DecNum = Convert.ToDecimal(stmtResult[1][1].ToString()); Assert.IsTrue(DecNum >= 46 && DecNum <= 47); Assert.AreEqual(stmtResult[1][2].ToString(), "20"); // TEST:0418. Removed DISTINCT from COUNT, with DISTINCT // count shall be 3, without it 12. sql = @" SELECT AVG(T1.COL4), AVG(T1.COL4 + T2.COL4), SUM(T2.COL4), COUNT(T1.COL4) FROM VTABLE T1, VTABLE T2;"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); Assert.AreEqual(1, stmtResult.Count); DecNum = Convert.ToDecimal(stmtResult[0][0].ToString()); Assert.IsTrue(DecNum >= 147 && DecNum <= 148); DecNum = Convert.ToDecimal(stmtResult[0][1].ToString()); Assert.IsTrue(DecNum >= 295 && DecNum <= 296); Assert.AreEqual(stmtResult[0][2].ToString(), "1772"); Assert.AreEqual(stmtResult[0][3].ToString(), "12"); #if false // dml075: TEST:0434. Just one interesting test, not // adding another method. // ERROR: "WHERE condition must be a blooean expression and no aggregation is allowed" sql = @" SELECT PNUM, SUM(HOURS) FROM WORKS GROUP BY PNUM HAVING EXISTS (SELECT PNAME FROM PROJ WHERE PROJ.PNUM = WORKS.PNUM AND SUM(WORKS.HOURS) > PROJ.BUDGET / 200);"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); Assert.AreEqual(2, stmtResult.Count); String Pnum1 = stmtResult[0][0].ToString(); String Pnum2 = stmtResult[1][0].ToString(); DecNum = Convert.ToDecimal(stmtResult[1][0].ToString()); decimal DecNum2 = Convert.ToDecimal(stmtResult[1][1].ToString()); /* * -- PASS:0434 If 2 rows selected with values (in any order):? * -- PASS:0434 PNUM = 'P1', SUM(HOURS) = 80? * -- PASS:0434 PNUM = 'P5', SUM(HOURS) = 92? */ Assert.IsTrue((Pnum1 == "P1" && DecNum == 80 && Pnum2 == "P5" && DecNum2 == 92) || (Pnum1 == "P5" && DecNum == 92 && Pnum2 == "P1" && DecNum2 == 80)); #endif // dml090 #if false /* BUG */ sql = @" SELECT MIN(PNAME) FROM PROJ, WORKS, STAFF WHERE PROJ.PNUM = WORKS.PNUM AND WORKS.EMPNUM = STAFF.EMPNUM AND BUDGET - GRADE * HOURS * 100 IN (-4400, -1000, 4000);"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); Assert.AreEqual(1, stmtResult.Count); Assert.AreEqual(stmtResult[0][0].ToString(), "CALM"); #endif sql = @" SELECT CITY, COUNT(*) FROM PROJ GROUP BY CITY HAVING (MAX(BUDGET) - MIN(BUDGET)) / 2 IN (2, 20000, 10000) ORDER BY CITY DESC;"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); Assert.AreEqual(2, stmtResult.Count); Assert.AreEqual(stmtResult[0][0].ToString(), "Vienna"); Assert.AreEqual(stmtResult[0][1].ToString(), "2"); Assert.AreEqual(stmtResult[1][0].ToString(), "Deale"); Assert.AreEqual(stmtResult[1][1].ToString(), "3"); sql = @" SELECT COUNT(*) FROM PROJ WHERE 24 * 1000 BETWEEN BUDGET - 5000 AND 50000 / 1.7;"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); Assert.AreEqual(stmtResult[0][0].ToString(), "3"); #if false /* * BUG: Expected one row: 'IRM' * Actual three rows: 'SDP'; 'SDP'; 'PAYR' */ sql = @" SELECT PNAME FROM PROJ WHERE 'Tampa' NOT BETWEEN CITY AND 'Vienna' AND PNUM > 'P2';"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); Assert.AreEqual(1, stmtResult.Count); Assert.AreEqual(stmtResult[0][0].ToString(), "IRM"); #endif sql = @" SELECT CITY, COUNT(*) FROM PROJ GROUP BY CITY HAVING 50000 + 2 BETWEEN 33000 AND SUM(BUDGET) - 20;"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); Assert.AreEqual(1, stmtResult.Count); Assert.AreEqual(stmtResult[0][0].ToString(), "Deale"); Assert.AreEqual(stmtResult[0][1].ToString(), "3"); // dml158 #if false /* BUG */ sql = @" SELECT EMPNUM, SUM (HOURS) FROM WORKS OWORKS GROUP BY EMPNUM HAVING EMPNUM IN ( SELECT WORKS.EMPNUM FROM WORKS JOIN STAFF ON WORKS.EMPNUM = STAFF.EMPNUM AND HOURS < SUM (OWORKS.HOURS) / 3 AND GRADE > 10) ORDER BY EMPNUM;"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); Assert.AreEqual(2, stmtResult.Count); Assert.AreEqual(stmtResult[0][0].ToString(), "E1"); Assert.AreEqual(stmtResult[0][1].ToString(), "184"); Assert.AreEqual(stmtResult[1][0].ToString(), "E4"); Assert.AreEqual(stmtResult[0][1].ToString(), "140"); sql = @" SELECT EMPNUM, SUM (HOURS) FROM WORKS OWORKS GROUP BY EMPNUM HAVING EMPNUM IN ( SELECT WORKS.EMPNUM FROM WORKS JOIN STAFF ON WORKS.EMPNUM = STAFF.EMPNUM AND HOURS >= 10 + AVG (OWORKS.HOURS) AND CITY = 'Deale') ORDER BY EMPNUM;"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); Assert.AreEqual(2, stmtResult.Count); Assert.AreEqual(stmtResult[0][0].ToString(), "E1"); Assert.AreEqual(stmtResult[0][1].ToString(), "184"); Assert.AreEqual(stmtResult[1][0].ToString(), "E4"); Assert.AreEqual(stmtResult[0][1].ToString(), "140"); #endif }
public static MsExcel._Workbook Shashade(MsExcel.Application appExcel) { _Workbook ExcelBooks = null; appExcel.Visible = false; ExcelBooks = appExcel.Workbooks.Add(); string Sheet_Name = "站点数据"; _Worksheet ExcelSheets = TU.Add_Sheets(ExcelBooks, Sheet_Name);//添加一个sheet ExcelSheets.Name = Sheet_Name; System.Data.DataTable dalaopo = new System.Data.DataTable("dalaopo"); dalaopo.Columns.Add("站点", typeof(double)); // dalaopo.Columns.Add("降水数据", typeof(double)); System.Data.DataTable jieguoTB = new System.Data.DataTable(); double[] n = new double[] { 129, 135, 128, 132, 99, 113, 157, 81, 70, 111, 78, 138, 103, 88, 110, 91 }; double[] T = new double[] { 5, 10, 20, 50 }; for (int i = 0; i < n.Length; i++) { List <double> gailvL = new List <double>(); for (int j = 0; j < T.Length; j++) { double gailv = 1 - 50 / (n[i] * T[j]); gailvL.Add(gailv); } double[] gailvd = gailvL.ToArray(); string[] gailvS = MYTUW.TUW.doublearrTOstring(gailvd); System.Data.DataTable jieguo = MYTUW.TUW.ArToDT1(gailvS); jieguoTB.Merge(jieguo); } #region /* for (int i = 1; i < RawDataStr_A.Length; i++) * { * char[] seperators = { ',' }; * string[] R_str1 = TU.ParseStringTo_Array<string>(RawDataStr_A[i], seperators); * * double zhandian = Convert.ToDouble(R_str1[0]); * string jiangshuistr = Convert.ToString(R_str1[R_str1.Length -1]); * double jiangshui= Convert.ToDouble(R_str1[R_str1.Length - 1]); * if (Convert .ToDouble ( jiangshuistr) > 30000) * { * string housanwei = Convert.ToString(R_str1[R_str1.Length - 1]).Substring(Convert.ToString(R_str1[R_str1.Length - 1]).Length - 3); * // Console.WriteLine(housanwei); * if (Convert .ToDouble (housanwei)>500&& Convert.ToDouble(housanwei)!=700) * { * jiangshui = Convert.ToDouble(housanwei); * } * else * { * jiangshui = 1; * } * * } * dalaopo.Rows.Add(zhandian, jiangshui * 0.1); * * * } * var groupNew = from row in dalaopo.AsEnumerable() * group row by new * { * zhandian = (row.Field<double>("站点")) * } * into groupRes * orderby groupRes.Key.zhandian * select groupRes; * //把分完组的每个表格放入DS中 * DataSet RTB_Grp = new DataSet(); * foreach (var eachGroup in groupNew) * { * System.Data.DataTable TBMid1 = eachGroup.CopyToDataTable(); * * RTB_Grp.Tables.Add(TBMid1); * } * //对每天的数据进行处理并放入 Traffic_TB中RTB_Grp.Tables .Count * for (int i=0;i< RTB_Grp.Tables.Count; i++) * { * System.Data.DataTable meigezhandian = RTB_Grp.Tables[i]; * List<double> baoyurishu = new List<double>(); * List<double> quannianyuliang = new List<double>(); * for (int j=0;j < meigezhandian.Rows.Count;j++) * { * double yuliang = Convert.ToDouble(meigezhandian.Rows[j][1]); * quannianyuliang.Add(yuliang); * if (yuliang>50) * { * baoyurishu.Add(yuliang); * } * } * double baoyutianshu = baoyurishu.Count; * double baoyuliang = baoyurishu.Sum(); * double baoyuqiangdu = baoyuliang / baoyutianshu; * double zongyuliang = quannianyuliang.Sum(); * double baoyugongxianlv = baoyuliang / zongyuliang; * jieguoTB.Rows.Add(Convert.ToDouble(meigezhandian.Rows[0][0]), baoyutianshu, * baoyuliang, baoyuqiangdu, baoyugongxianlv); * } */ #endregion MYTUW.TUW.DTToExcel(jieguoTB, ExcelSheets, 2, 1); MYTUW.TUW.Delete_sheet(appExcel, ExcelBooks); return(ExcelBooks); }
public void TestStringCompare() { string sql = "create table str1(col1 char(20), col2 varchar(25), col3 int);"; System.Collections.Generic.List <physic.Row> stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "create table str2(col1 char(25), col2 varchar(35), col3 int);"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "insert into str1 values('Hamilton', 'Brisbane', 101);"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "insert into str1 values('pavillion', 'samsung', 11);"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "insert into str1 values('Blazer', 'Roger', 201);"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "insert into str1 values('North Rim', 'South Bay', 221);"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "insert into str1 values('Logitech', 'Brookstone', 101);"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "insert into str1 values('Peru', 'Lama', 501);"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "insert into str1 values('Bolivia', 'Lama', 501);"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "insert into str1 values('North Rim', 'Brookstone', 786);"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "insert into str1 values('pavillion', 'Civilian', 786);"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "insert into str1 values('Quadruple', 'South Bay', 786);"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "insert into str1 values('Jalandhar', 'Beaverton', 601);"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "insert into str1 values('Mercury', 'Hercules', 117);"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "insert into str1 values('Maryland', 'Palm Beach', 219);"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "insert into str1 values('Nicholas', 'Simplex', 769);"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "insert into str1 values('Big Basin', 'Victoria', 139);"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "insert into str1 values('Bolivia', 'South Bay', 801);"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "insert into str2 values('Peru', 'Quangos', 501)"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "insert into str2 values('Bolivia', 'Frenyando', 1067)"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "insert into str2 values('North Rim', 'Danjamyla', 786)"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "insert into str2 values('thathomadip', 'Beaverton', 876)"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "insert into str2 values('Palm Beach', 'Tholapza zuseri', 1786)"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "insert into str2 values('Kuymugian', 'Maryland', 7861)"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "insert into str2 values('pendom dedsom', 'Civilian', 786)"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "insert into str2 values('Ablutomania', 'Nicholas', 6981)"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "insert into str2 values('pavillion', 'Medallion', 9984)"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "insert into str2 values('Bettalian', 'Mandalorian', 1089)"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "insert into str2 values('Hercules', 'Radio City', 3012)"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "insert into str2 values('Nalaze Simirethy', 'Barcelona', 3012)"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "insert into str2 values('Via kawethibun', 'Jalandhar', 3421)"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "insert into str2 values('Shangrila', 'Minar Tirth', 4215)"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "insert into str2 values('Big Basin', 'Vishaishil', 1089)"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "insert into str2 values('Ashekkaza', 'Vishaishil', 1439)"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "insert into str2 values('Ashekkaza', 'Santa Rita', 2139)"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "insert into str2 values('Kuymugian', 'Kudirosif', 7861)"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); sql = "select * from str1 where col1 = 'Hamilton' order by 1;"; TU.ExecuteSQL(sql, "Hamilton,Brisbane,101"); Assert.AreEqual("", TU.error_); sql = "select * from str1 where col1 >= 'Cashmere' and col2 <= 'Lama' order by 1;"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); Assert.AreEqual(7, stmtResult.Count); Assert.AreEqual(stmtResult[0][0].ToString(), "Hamilton"); Assert.AreEqual(stmtResult[6][0].ToString(), "Peru"); sql = "select * from str1 where col2 > 'Lama' order by 1;"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); Assert.AreEqual(8, stmtResult.Count); Assert.AreEqual(stmtResult[0][0].ToString(), "Big Basin"); Assert.AreEqual(stmtResult[7][0].ToString(), "Quadruple"); sql = "select * from str1 where 'Lama' < col2 order by 1;"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); Assert.AreEqual(8, stmtResult.Count); Assert.AreEqual(stmtResult[0][0].ToString(), "Big Basin"); Assert.AreEqual(stmtResult[7][0].ToString(), "Quadruple"); sql = "select * from str1 where 'Mama' <= 'Lama' order by 1;"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); Assert.AreEqual(0, stmtResult.Count); sql = "select * from str1 where 'Mama' >= 'Lama' order by 1;"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); Assert.AreEqual(16, stmtResult.Count); Assert.AreEqual(stmtResult[0][0].ToString(), "Big Basin"); Assert.AreEqual(stmtResult[15][0].ToString(), "Quadruple"); sql = "select * from str1 where col1 between 'abba' and 'Dhaba' order by 1;"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); Assert.AreEqual(4, stmtResult.Count); Assert.AreEqual(stmtResult[0][0].ToString(), "Big Basin"); Assert.AreEqual(stmtResult[3][0].ToString(), "Bolivia"); sql = "select col1 from str1 where col1 > 'Logic' group by col1 order by 1;"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); Assert.AreEqual(8, stmtResult.Count); Assert.AreEqual(stmtResult[0][0].ToString(), "Logitech"); Assert.AreEqual(stmtResult[7][0].ToString(), "Quadruple"); sql = "select col1 from str1 where col1 > 'Logic' group by col1 having count(*) > 1 order by 1;"; TU.ExecuteSQL(sql, "North Rim;pavillion"); Assert.AreEqual("", TU.error_); sql = "select t1.col1, sum(t2.col3) from str1 t1, str2 t2 where t1.col1 = t2.col1 or t1.col2 = t2.col2 group by t1.col1 order by 1"; TU.ExecuteSQL(sql, "Big Basin,1089;Bolivia,2134;Jalandhar,876;North Rim,1572;pavillion,20754;Peru,501"); Assert.AreEqual("", TU.error_); sql = "select t1.col1, t2.col2, sum(t1.col3 + t2.col3) from str1 t1, str2 t2 where t1.col1 = t2.col1 and t1.col1 > 'Kaiser' and t2.col2 < 'Valhalla' group by t1.col1, t2.col2 order by 1;"; TU.ExecuteSQL(sql, "North Rim,Danjamyla,2579;pavillion,Medallion,20765;Peru,Quangos,1002"); Assert.AreEqual("", TU.error_); sql = "select t1.col1, count(t1.col1) from str1 t1, str2 t2 where t1.col1 <> t2.col1 and t1.col2 <> t2.col2 group by t1.col1 order by 1;"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); Assert.AreEqual(13, stmtResult.Count); Assert.AreEqual(stmtResult[0][0].ToString(), "Big Basin"); Assert.AreEqual(stmtResult[12][0].ToString(), "Quadruple"); sql = "select t1.col2, count(t1.col2) from str1 t1, str2 t2 where t1.col1 <> t2.col1 and t1.col2 <> t2.col2 group by t1.col2 order by 1;"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); Assert.AreEqual(12, stmtResult.Count); Assert.AreEqual(stmtResult[0][0].ToString(), "Beaverton"); Assert.AreEqual(stmtResult[11][0].ToString(), "Victoria"); sql = "select col1 || col2 as col12, col1 || '_Suffix', 'Prefix_' || col2, 'Prefix_' || '_Suffix' from str1;"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); Assert.AreEqual(16, stmtResult.Count); Assert.AreEqual(stmtResult[0][0].ToString(), "HamiltonBrisbane"); Assert.AreEqual(stmtResult[15][0].ToString(), "BoliviaSouth Bay"); sql = "select count(col1), count(col2), sum(col3) from str2;"; TU.ExecuteSQL(sql, "18,18,57905"); Assert.AreEqual("", TU.error_); stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); Assert.AreEqual(1, stmtResult.Count); sql = "select sum(col1) from str1;"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual("", TU.error_); Assert.AreEqual(1, stmtResult.Count); Assert.IsTrue(stmtResult[0].ToString().Contains("RimLogitechPeruBoliviaNorth")); /* Errors. */ #if false /* Runtime excptions. */ sql = "select min(col1) from str1;"; stmtResult = TU.ExecuteSQL(sql); Assert.IsTrue(TU.error_.Contains("Operator '>' cannot be applied to")); sql = "select max(col2) from str1;"; stmtResult = TU.ExecuteSQL(sql); Assert.IsTrue(TU.error_.Contains("Operator '>' cannot be applied to")); sql = "select avg(col1) from str1;"; stmtResult = TU.ExecuteSQL(sql); Assert.IsTrue(TU.error_.Contains("Operator '>' cannot be applied to")); #endif sql = "select * from str1 where col1 > 100;"; stmtResult = TU.ExecuteSQL(sql); Assert.IsTrue(TU.error_.Contains("no implicit conversion of")); sql = "select * from str2 where col2 < 100;"; stmtResult = TU.ExecuteSQL(sql); Assert.IsTrue(TU.error_.Contains("no implicit conversion of")); sql = "select * from str2 where col2 = 100;"; stmtResult = TU.ExecuteSQL(sql); Assert.IsTrue(TU.error_.Contains("no implicit conversion of")); sql = "select * from str1 where col2 <> 100;"; stmtResult = TU.ExecuteSQL(sql); Assert.IsTrue(TU.error_.Contains("no implicit conversion of")); sql = "select * from str1 where col2 <= 100;"; stmtResult = TU.ExecuteSQL(sql); Assert.IsTrue(TU.error_.Contains("no implicit conversion of")); sql = "select * from str2 where col2 >= 100;"; stmtResult = TU.ExecuteSQL(sql); Assert.IsTrue(TU.error_.Contains("no implicit conversion of")); }
public void dml014() { var sql = @" SELECT PNUM FROM PROJ WHERE BUDGET BETWEEN 40000 AND 60000;"; var stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual(1, stmtResult.Count); Assert.AreEqual(stmtResult[0][0].ToString(), "P6"); sql = @" SELECT PNUM FROM PROJ WHERE BUDGET >= 40000 AND BUDGET <= 60000;"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual(1, stmtResult.Count); Assert.AreEqual(stmtResult[0][0].ToString(), "P6"); /* * BUG: Should return only one row, with 'Vienna' * but returns four: Deale, Vienna, Deale, Akorn. * Suppress Asserts for now. */ sql = @" SELECT CITY FROM STAFF WHERE GRADE NOT BETWEEN 12 AND 13;"; stmtResult = TU.ExecuteSQL(sql); // Assert.AreEqual(1, stmtResult.Count); // Assert.AreEqual(stmtResult[0][0].ToString(), "Vienna"); #if false /* BUG or Unsupported WHER NOT ()? */ sql = @" SELECT CITY FROM STAFF WHERE NOT(GRADE BETWEEN 12 AND 13);"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual(1, stmtResult.Count); Assert.AreEqual(stmtResult[0][0].ToString(), "Vienna"); #endif sql = @" SELECT STAFF.EMPNAME FROM STAFF WHERE STAFF.EMPNUM IN (SELECT WORKS.EMPNUM FROM WORKS WHERE WORKS.PNUM IN (SELECT PROJ.PNUM FROM PROJ WHERE PROJ.CITY='Tampa'));"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual(1, stmtResult.Count); Assert.AreEqual(stmtResult[0][0].ToString(), "Alice"); sql = @" SELECT WORKS.HOURS FROM WORKS WHERE WORKS.PNUM NOT IN (SELECT PROJ.PNUM FROM PROJ WHERE PROJ.BUDGET BETWEEN 5000 AND 40000);"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual(1, stmtResult.Count); Assert.AreEqual(stmtResult[0][0].ToString(), "12"); sql = @" SELECT WORKS.HOURS FROM WORKS WHERE NOT (WORKS.PNUM IN (SELECT PROJ.PNUM FROM PROJ WHERE PROJ.BUDGET BETWEEN 5000 AND 40000));"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual(1, stmtResult.Count); Assert.AreEqual(stmtResult[0][0].ToString(), "12"); sql = @" SELECT HOURS FROM WORKS WHERE PNUM NOT IN (SELECT PNUM FROM WORKS WHERE PNUM IN ('P1','P2','P4','P5','P6'));"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual(1, stmtResult.Count); Assert.AreEqual(stmtResult[0][0].ToString(), "80"); sql = @" SELECT HOURS FROM WORKS WHERE NOT (PNUM IN (SELECT PNUM FROM WORKS WHERE PNUM IN ('P1','P2','P4','P5','P6')));"; stmtResult = TU.ExecuteSQL(sql); Assert.AreEqual(1, stmtResult.Count); Assert.AreEqual(stmtResult[0][0].ToString(), "80"); /* * BUG: Should return one row with Alice but retuns 5 rows. */ sql = @" SELECT STAFF.EMPNAME FROM STAFF WHERE NOT EXISTS (SELECT * FROM PROJ WHERE NOT EXISTS (SELECT * FROM WORKS WHERE STAFF.EMPNUM = WORKS.EMPNUM AND WORKS.PNUM=PROJ.PNUM));"; stmtResult = TU.ExecuteSQL(sql); // Assert.AreEqual(1, stmtResult.Count); // Assert.AreEqual(stmtResult[0][0].ToString(), "Alice"); }
public void DeleteTU(TU tu) { Task.Run(async() => await Model.Singleton().DeleteTU(tu)); }
void Build_Click(object sender, EventArgs e) { Propagate(); TU.Build(); }