protected string rowsSql(string queryName, string orderAlias
, out string sqlOrderBy, out string sqlOrderWith)
{
string sql = "", baseSqlFormat;
string sqlWhereJoin = "", sqlWhereCond = "";
string sqlOrderJoin = "", orderByFields2query = "";
sqlOrderBy = "";
sqlOrderWith = "";
Dictionary <string, int> fieldDict =
new Dictionary <string, int>();
// base part
baseSqlFormat = @"
select r.rowId{0}
from [rows] r
join queries q on r.tableId=q.tableId
join expressions e on q.whereExpressionId=e.expressionId
";
// orderBy part
DataTable dtOrder = orderByFields(queryName);
int orderFieldNum = 0;
foreach (DataRow dr in dtOrder.Rows)
{
orderFieldNum++;
string fieldId = dr["fieldId"] + "";
string ascDesc = dr["ascDesc"] + "";
if (!fieldDict.ContainsKey(fieldId))
{
fieldDict.Add(fieldId, fieldDict.Count + 1);
}
sqlOrderJoin += string.Format(@"
join queryFields qf{0} on qf{0}.queryId=q.queryId and qf{0}.orderByOrder={0}
join fieldValues fv{0} on r.rowId=fv{0}.rowId and qf{0}.fieldId=fv{0}.fieldId
", orderFieldNum);
if (orderFieldNum == 1)
{
sqlOrderBy = $"order by {orderAlias}.fieldValue1 {ascDesc}";
sqlOrderWith = ", fieldValue1";
orderByFields2query = ", fv1.fieldValue fieldValue1";
}
else
{
sqlOrderBy += $",{orderAlias}.fieldValue{orderFieldNum} {ascDesc}";
sqlOrderWith += $", fieldValue{orderFieldNum}";
orderByFields2query += $", fv{orderFieldNum}.fieldValue fieldValue{orderFieldNum}";
}
}
// where part
List <queryWhereRec> whereList = whereConditions(queryName);
if (whereList == null || whereList.Count <= 0)
{
throw new Exception("bad where condition");
}
// add wheres to dictionary
Dictionary <string, queryWhereRec> whereExpressDict =
new Dictionary <string, queryWhereRec>();
//find whereExpressionId
string whereExpressId = "";
queryWhereRec theWhereRec = null;
foreach (queryWhereRec rec in whereList)
{
if (rec.isWhereExpressId)
{
if (!string.IsNullOrWhiteSpace(whereExpressId) &&
theWhereRec != null)
{
throw new Exception("where expression id already assigned");
}
whereExpressId = rec.expressionId.ToString();
theWhereRec = rec;
}
whereExpressDict.Add(rec.expressionId.ToString(), rec);
}
//get expression by whereExpressionId
//DataRow drWhere = whereList.Rows[0];
//string expressionId = drWhere["expressionId"] + "";//new
//string operatorName = drWhere["operatorName"] + "";
//string stringInSourceCode = drWhere["stringInSourceCode"] + "";
//string isPrefix = drWhere["isPrefix"] + "";
//string paraNum = drWhere["paraNum"] + "";
//string paraField1id = drWhere["paraField1id"] + "";
//string field1Name = drWhere["field1Name"] + "";//restored
//string paraField2id = drWhere["paraField2id"] + "";
//string field2Name = drWhere["field2Name"] + "";
//string para2externalName = drWhere["para2externalName"] + "";
//string subExpression1Id = drWhere["subExpression1Id"] + "";//new
//string subExpression2Id = drWhere["subExpression2Id"] + "";//new
//int caseNo = 0;
//if (!string.IsNullOrWhiteSpace(paraField1id) &&
// //!string.IsNullOrWhiteSpace(field1Name) &&
// paraField2id.Length == 0 &&
// field2Name.Length == 0 &&
// !string.IsNullOrWhiteSpace(para2externalName))
//{
// caseNo = 1;
// if (paraNum.CompareTo("2") != 0)
// throw new Exception($"wrong paraNum {paraNum} (case#{caseNo})");
string sqlExpr = sqlExpression(whereExpressId, whereExpressDict, ref fieldDict);
sqlWhereCond = string.Format(@"
where q.queryName=@queryName and {0}
", sqlExpr);
foreach (KeyValuePair <string, int> rec in fieldDict)
{
if (rec.Value > orderFieldNum)
{
sqlWhereJoin += $@"
join fieldValues fv{rec.Value} on r.rowId=fv{rec.Value}.rowId and fv{rec.Value}.fieldId='{rec.Key}'
";
}
}
//}
//else
// throw new Exception("where condition not dealing with");
// final
sql = string.Format(baseSqlFormat, orderByFields2query) + sqlWhereJoin + sqlOrderJoin + sqlWhereCond;// + sqlOrderBy;
return(sql);
}
protected static string sqlExpression(
//queryWhereRec theWhereRec, // no need
string whereExpressId,
//string para1, // no need
Dictionary <string, queryWhereRec> whereDict,
ref Dictionary <string, int> whereFieldDict
)
//string operatorName
//, string stringInSourceCode, string isPrefix
//, string paraNum, string para1, string para2)
{
string sql = "";
queryWhereRec rec = whereDict[whereExpressId];
string para1 = "", para2 = "";
bool needQuote = false;
if (!string.IsNullOrWhiteSpace(rec.subExpression1Id.ToString()))
{
para1 = sqlExpression(rec.subExpression1Id.ToString(), whereDict, ref whereFieldDict);
}
else if (!string.IsNullOrWhiteSpace(rec.paraField1id.ToString()))
{
para1 = fieldId2para(rec.paraField1id.ToString(), ref whereFieldDict);
needQuote = true;
}
//else if (!string.IsNullOrWhiteSpace(rec.para2externalName))
// para1 = rec.expressionId;
else
{
throw new Exception("para1 not defined!");
}
if (!string.IsNullOrWhiteSpace(rec.subExpression2Id.ToString()))
{
para2 = sqlExpression(rec.subExpression2Id.ToString(), whereDict, ref whereFieldDict);
}
else if (!string.IsNullOrWhiteSpace(rec.paraField2id.ToString()))
{
para2 = fieldId2para(rec.paraField2id.ToString(), ref whereFieldDict);
}
else if (!string.IsNullOrWhiteSpace(rec.para2externalName))
{
para2 = "@" + rec.para2externalName;
//if (needQuote)
// para2 = $"'{rec.expressionId.ToString()}'";
//else
// para2 = rec.expressionId.ToString();
}
else
{
throw new Exception("para2 not defined!");
}
if (rec.paraNum == 2)
{
sql = $"({para1} {rec.stringInSourceCode} {para2})";
}
else
{
if (rec.isPrefix) //== "1")
{
sql = $"({rec.stringInSourceCode} {para1})";
}
else
{
sql = $"({para1} {rec.stringInSourceCode})";
}
}
return(sql);
}
