private static string GetSchemaList(string dtb, string user)
{
string sErr = "";
string result = Properties.Resources.SCHEMALIST;
CoreQD_SCHEMAControl ctr = new CoreQD_SCHEMAControl();
DataTable dt = ctr.GetAll(dtb, ref sErr);
if (user != "TVC")
{
sErr = BUS.CoreDAControl.SetDataAccessGroup("DAG", dt, user);
}
XmlDocument doc = new XmlDocument();
doc.LoadXml(result);
XmlElement docele = doc.DocumentElement;
foreach (DataRow row in dt.Rows)
{
//<row table="AllMovement" Description="All Movement" module="AllMovement"/>
if (row["SCHEMA_STATUS"].ToString().Trim() == "Y")
{
XmlElement ele = doc.CreateElement("row");
ele.SetAttribute("table", row["SCHEMA_ID"].ToString());
ele.SetAttribute("Description", row["DESCRIPTN"].ToString());
ele.SetAttribute("module", row["SCHEMA_ID"].ToString());
ele.SetAttribute("lookup", row["LOOK_UP"].ToString());
docele.AppendChild(ele);
}
}
result = doc.InnerXml;
return(result);
}
private static string GetJoinSchema(string dtb)
{
string sErr = "";
string result = System.Convert.ToString(Properties.Resources.ResourceManager.GetObject(JoinsDefinition));
CoreQD_SCHEMAControl ctr = new CoreQD_SCHEMAControl();
DataTable value = ctr.GetJoins(dtb, ref sErr);
string tmp = "";
if (value.Rows.Count > 0)
{
foreach (DataRow row in value.Rows)
{
if (!row[0].ToString().Contains("schema"))
{
if (row[0].ToString()[0] == '<')
{
tmp += row[0].ToString();
}
else
{
tmp += Base64ToString(row[0].ToString());
}
}
}
//return result;
}
//tmp = Base64ToString(tmp);
XmlDocument doc = new XmlDocument();
doc.LoadXml(result);
XmlElement docele = doc.DocumentElement;
if (tmp != "")
{
docele.InnerXml = tmp;
return(doc.InnerXml);
}
DataTable dt = ctr.GetAll(dtb, ref sErr);
foreach (DataRow irow in dt.Rows)
{
//<row fromcode ="Movement" lookup="(SELECT * FROM TVC_MFMOVXXX WHERE Not Hold='Y')"/>
string schema = irow["FROM_TEXT"].ToString();
DataSet dset = new DataSet("Schema");
DataTable dtfrom = new DataTable("fromcode");
DataColumn[] col = new DataColumn[] { new DataColumn("fromcode"), new DataColumn("lookup") };
dtfrom.Columns.AddRange(col);
dset.Tables.Add(dtfrom);
StringReader strR = new StringReader(schema);
dset.ReadXml(strR);
strR.Close();
string newValue = "";
foreach (DataRow jrow in dtfrom.Rows)
{
//newValue += string.Format("<row fromcode=\"{0}\" lookup=\"{1}\"/> ", jrow["fromcode"], jrow["lookup"]);
XmlElement ele = doc.CreateElement("row");
ele.SetAttribute("fromcode", jrow["fromcode"].ToString());
ele.SetAttribute("lookup", jrow["lookup"].ToString());
docele.AppendChild(ele);
}
//CoreQD_SCHEMAInfo info = new CoreQD_SCHEMAInfo(irow);
//if (info.FIELD_TEXT != "")
//{
// info.FROM_TEXT = newValue;
// ctr.Update(info);
//}
}
result = doc.InnerXml;
return(result);
}
private static string GetTableSchema(string dtb, string origin)
{
string sErr = "";
string result = "";
CoreQD_SCHEMAControl ctr = new CoreQD_SCHEMAControl();
string scheme = ctr.GetField(dtb, origin, ref sErr);
if (scheme != "")
{
return(scheme);
}
CoreQD_SCHEMAInfo inf = ctr.Get(dtb, origin, ref sErr);
if (inf.SCHEMA_ID == "")
{
result = System.Convert.ToString(Properties.Resources.ResourceManager.GetObject(origin));
}
else
{
result = "<?xml version='1.0' encoding='utf-8' ?><SUN_SCHEMA></SUN_SCHEMA>";
XmlDocument doc = new XmlDocument();
doc.LoadXml(result);
XmlElement docele = doc.DocumentElement;
//<row table="M5" node="Lookup" name="Lookup" type=""/>
string schema = inf.FROM_TEXT;
DataSet dset = new DataSet("Schema");
//DataTable dtfrom = new DataTable("fromcode");
//DataColumn[] colfrom = new DataColumn[] { new DataColumn("fromcode"), new DataColumn("lookup") };
//dtfrom.Columns.AddRange(colfrom);
DataTable dtfield = new DataTable("field");
DataColumn[] colfield = new DataColumn[] { new DataColumn("node")
, new DataColumn("table")
, new DataColumn("name")
, new DataColumn("nodeDesc")
, new DataColumn("type") };
dtfield.Columns.AddRange(colfield);
//dset.Tables.Add(dtfrom);
dset.Tables.Add(dtfield);
//DataRelation relation = new DataRelation("R_field", dtfrom.Columns["fromcode"], dtfield.Columns["table"], true);
//dset.Relations.Add(relation);
StringReader strR = new StringReader(schema);
dset.ReadXml(strR);
strR.Close();
//dset.ReadXml(schema);
foreach (DataRow jrow in dtfield.Rows)
{
XmlElement ele = doc.CreateElement("row");
ele.SetAttribute("table", jrow["table"].ToString());
ele.SetAttribute("node", jrow["node"].ToString());
ele.SetAttribute("name", jrow["name"].ToString());
ele.SetAttribute("nodeDesc", jrow["nodeDesc"] == null ? "" : jrow["nodeDesc"].ToString());
ele.SetAttribute("type", jrow["type"] == null ? "" : jrow["type"].ToString());
ele.SetAttribute("conn_id", inf.DEFAULT_CONN);
docele.AppendChild(ele);
}
result = doc.InnerXml;
//inf.FIELD_TEXT = result;
//ctr.Update(inf);
}
return(result);
}
/// <summary>
/// Step2 :Parse TTformular to sqlBuilder Object
/// </summary>
/// <param name="ParseString">TTformular </param>
/// <param name="_SQLBuilder">sqlBuilder Object</param>
/// <remarks></remarks>
public static void Formular2SQLBuilder(string ParseString, ref SQLBuilder _SQLBuilder)
{
if (string.IsNullOrEmpty(ParseString))
{
return;
}
ParseString = Regex.Replace(ParseString, ".*(?=TT_XLB_EB)", string.Empty);
string[] FromTo = new string[] { };
string vParamsString = null; // the string, contains parameters in TTformular
string[] vParameter = new string[] { }; // array of all parameters ex .$H$1,$G14,$H14,J$11,J$11
int vparacount = 0;
string vPosition = null; // address of the formular
// Return Database, table
string vTable = null;
string vDatabase = null;
vDatabase = Regex.Match(ParseString, regexDB).Value;
vTable = Regex.Match(ParseString, regexTable).Value;
_SQLBuilder.Table = vTable;
string vFilter = null; // ex part of TTformular , contains filters
vFilter = Regex.Match(ParseString, @"(?<=\,K\=)[^\,,.]+").Value;
int i = 0;
int n = Regex.Matches(ParseString, @"F\=.*?,K").Count;
// fill FromTo array
if (n > 0)
{
FromTo = new string[n];
foreach (Match ft in Regex.Matches(ParseString, @"F\=.*?,K"))
{
FromTo[i] = ft.Value;
i = i + 1;
}
}
n = 0;
i = 0;
// get string , contains parameters
vParamsString = Regex.Match(ParseString, String.Format(@"\{0}\,.+?\)", System.Convert.ToChar(34))).Value;
// fill to parameter Array
if (!(string.IsNullOrEmpty(vParamsString)))
{
vParamsString = /* TRANSINFO: .NET Equivalent of Microsoft.VisualBasic NameSpace */ vParamsString.Substring(1);
vParamsString = vParamsString.Substring(1, vParamsString.Length - 2); // Strings.Mid(vParamsString, 1, /* TRANSINFO: .NET Equivalent of Microsoft.VisualBasic NameSpace */ vParamsString.Length - 1);
vParamsString = vParamsString + ","; // them dau , cho de xu ly
n = Regex.Matches(vParamsString, ".*?,").Count; // cac tham so
if (n > 0)
{
vParameter = new string[n]; // tham so dau tien la vi tri cua cong thuc
foreach (Match p in Regex.Matches(vParamsString, ".*?,"))
{
i = i + 1;
if (i == 1)
{
vPosition = p.Value.Replace(",", string.Empty);
_SQLBuilder.Pos = vPosition;
}
else
{
vParameter[i - 1] = p.Value.Replace(",", string.Empty);
}
}
}
}
if (vDatabase.Contains("{P}"))
{
_SQLBuilder.DatabaseP = vDatabase;
_SQLBuilder.Database = vParameter[System.Convert.ToInt32(double.Parse(vDatabase.Replace("{P}", string.Empty)))];
vparacount = vparacount + 1;
_SQLBuilder.DatabaseV = _SQLBuilder.ParaValueList[vparacount];
}
else
{
_SQLBuilder.Database = vDatabase;
_SQLBuilder.DatabaseV = vDatabase;
_SQLBuilder.DatabaseP = "";
}
i = 0;
string vf = null;
string vt = null;
string vf1 = "";
string vt1 = "";
string filterf = null;
string filtert = null;
// identifying filters
MatchCollection matchCollect = Regex.Matches(ParseString, @"(?<=\,K\=)[^\,,.]+");
foreach (Match m in matchCollect)
{
vFilter = m.Value;
vf = Regex.Match(FromTo[i], "F=.*?,").Value;
if (!(string.IsNullOrEmpty(vf)))
{
vf = /* TRANSINFO: .NET Equivalent of Microsoft.VisualBasic NameSpace */ vf.Substring(2);
vf = vf.Substring(0, vf.Length - 1);// Strings.Mid(vf, 1, /* TRANSINFO: .NET Equivalent of Microsoft.VisualBasic NameSpace */ vf.Length - 1);
}
//if (vf != "")
// vt = Regex.Match(FromTo[i].Replace(vf, "_"), "T.+?,").Value.ToString();
//else
vt = Regex.Match(FromTo[i], "T=.*?,").Value.ToString();
if (!(string.IsNullOrEmpty(vt)))
{
vt = /* TRANSINFO: .NET Equivalent of Microsoft.VisualBasic NameSpace */ vt.Substring(2);
vt = vt.Substring(0, vt.Length - 1);// Strings.Mid(vt, 1, /* TRANSINFO: .NET Equivalent of Microsoft.VisualBasic NameSpace */ vt.Length - 1);
}
filterf = "";
filtert = "";
if (Regex.IsMatch(vf, "{P}"))
{
filterf = vParameter[System.Convert.ToInt32(double.Parse(/* TRANSINFO: .NET Equivalent of Microsoft.VisualBasic NameSpace */ vf.Substring(3)))];
}
if (Regex.IsMatch(vt, "{P}"))
{
filtert = vParameter[System.Convert.ToInt32(double.Parse(/* TRANSINFO: .NET Equivalent of Microsoft.VisualBasic NameSpace */ vt.Substring(3)))];
}
if (!(string.IsNullOrEmpty(vFilter)))
{
vFilter = /* TRANSINFO: .NET Equivalent of Microsoft.VisualBasic NameSpace */ vFilter.Substring(1);
if (string.IsNullOrEmpty(filterf))
{
filterf = vf;
}
if (string.IsNullOrEmpty(filtert))
{
filtert = vt;
}
if (vFilter.ToUpper() == "LA/LEDGER")
{ // ledger lam rieng
if (Regex.IsMatch(vf, "{P}"))
{
_SQLBuilder.LedgerP = vf;
_SQLBuilder.Ledger = vParameter[System.Convert.ToInt32(double.Parse(/* TRANSINFO: .NET Equivalent of Microsoft.VisualBasic NameSpace */ vf.Substring(3)))];
vparacount = vparacount + 1;
_SQLBuilder.LedgerV = _SQLBuilder.ParaValueList[vparacount];
}
else
{
_SQLBuilder.Ledger = vf;
_SQLBuilder.LedgerV = vf;
_SQLBuilder.LedgerP = "";
}
}
else
{
if (Regex.IsMatch(vf, "{P}"))
{
vparacount = vparacount + 1;
vf1 = _SQLBuilder.ParaValueList[vparacount]; // gia tri
}
else
{
vf1 = vf;
}
if (Regex.IsMatch(vt, "{P}"))
{
vparacount = vparacount + 1;
vt1 = _SQLBuilder.ParaValueList[vparacount]; // gia tri
}
else
{
vt1 = vt;
}
foreach (Node _node in SchemaDefinition.GetDecorateTableByCode(vTable, _SQLBuilder.Database))
{
if (_node.Code == vFilter)
{
_SQLBuilder.Filters.Add(new Filter(new Node(vFilter, _node.Description), filterf, filtert, vf1, vt1, vf, vt));
// _SQLBuilder.SelectedNodes.Add(New Node(vOutputAgr(i), Output, _node.Description, _node.FType))
}
}
// _SQLBuilder.Filters.Add(New Filter(New Node(vFilter, vFilter), filterf, filtert, vf1, vt1, vf, vt))
}
}
i = i + 1;
}
string Output = null;
string[] vOutputAgr = null;
n = Regex.Matches(ParseString, @"E\=.+?,").Count;
i = 0;
if (n > 0)
{
vOutputAgr = new string[n];
foreach (Match oe in Regex.Matches(ParseString, @"E\=.+?,"))
{
vOutputAgr[i] = oe.Value.ToString().Substring(2, 1);// Strings.Mid(oe.Value.ToString(), 3, 1);
i = i + 1;
}
i = 0;
foreach (Match o in Regex.Matches(ParseString, @"O\=.+?,"))
{
Output = o.Value.ToString();
if (!(string.IsNullOrEmpty(Output)))
{
Output = Output.Replace(",", string.Empty);
}
vOutputAgr[i] = AgregateN2Code(vOutputAgr[i]);
if (!(string.IsNullOrEmpty(Output)))
{
Output = Output.Replace("O=/", string.Empty);
foreach (Node _node in SchemaDefinition.GetDecorateTableByCode(vTable, _SQLBuilder.Database))
{
// If Regex.IsMatch(_node.Code, Output & "$") Then
if (_node.Code.ToUpper() == Output.ToUpper())
{
_SQLBuilder.SelectedNodes.Add(new Node(vOutputAgr[i], Output, _node.Description, _node.FType, _node.NodeDesc));
break; /* TRANSWARNING: check that break is in correct scope */
}
}
}
i = i + 1;
}
}
string sErr = "";
CoreQD_SCHEMAControl schctr = new CoreQD_SCHEMAControl();
CoreQD_SCHEMAInfo schInf = schctr.Get(_SQLBuilder.Database, _SQLBuilder.Table, ref sErr);
_SQLBuilder.ConnID = schInf.DEFAULT_CONN;
}