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