public DataTableCollection Get_Page(CoreQD_SCHEMAInfo obj, string orderBy, int pageIndex, int pageSize, ref String sErr)
{
string whereClause = CreateWhereClause(obj);
DataTableCollection dtList = null; try
{
connect();
InitSPCommand(_strSPGetPages);
AddParameter("WhereClause", whereClause);
AddParameter("OrderBy", orderBy);
AddParameter("PageIndex", pageIndex);
AddParameter("PageSize", pageSize);
dtList = executeCollectSelectSP();
}
catch (Exception ex)
{
sErr = ex.Message;
}
disconnect();
if (sErr != "")
{
CoreErrorLog.SetLog(sErr);
}
return(dtList);
}
public string Update(CoreQD_SCHEMAInfo objEntr)
{
string sErr = "";
try
{
connect();
InitSPCommand(_strSPUpdateName);
AddParameter(CoreQD_SCHEMAInfo.Field.CONN_ID.ToString(), objEntr.CONN_ID);
AddParameter(CoreQD_SCHEMAInfo.Field.SCHEMA_ID.ToString(), objEntr.SCHEMA_ID);
AddParameter(CoreQD_SCHEMAInfo.Field.LOOK_UP.ToString(), objEntr.LOOK_UP);
AddParameter(CoreQD_SCHEMAInfo.Field.DESCRIPTN.ToString(), objEntr.DESCRIPTN);
AddParameter(CoreQD_SCHEMAInfo.Field.FIELD_TEXT.ToString(), objEntr.FIELD_TEXT);
AddParameter(CoreQD_SCHEMAInfo.Field.FROM_TEXT.ToString(), objEntr.FROM_TEXT);
AddParameter(CoreQD_SCHEMAInfo.Field.DAG.ToString(), objEntr.DAG);
AddParameter(CoreQD_SCHEMAInfo.Field.SCHEMA_STATUS.ToString(), objEntr.SCHEMA_STATUS);
AddParameter(CoreQD_SCHEMAInfo.Field.UPDATED.ToString(), objEntr.UPDATED);
AddParameter(CoreQD_SCHEMAInfo.Field.ENTER_BY.ToString(), objEntr.ENTER_BY);
AddParameter(CoreQD_SCHEMAInfo.Field.DEFAULT_CONN.ToString(), objEntr.DEFAULT_CONN);
excuteSPNonQuery();
}
catch (Exception ex)
{
sErr = ex.Message;
}
disconnect();
if (sErr != "")
{
CoreErrorLog.SetLog(sErr);
}
return(sErr);
}
public CoreQD_SCHEMAInfo(CoreQD_SCHEMAInfo objEntr)
{
_CONN_ID = objEntr.CONN_ID;
_SCHEMA_ID = objEntr.SCHEMA_ID;
_LOOK_UP = objEntr.LOOK_UP;
_DESCRIPTN = objEntr.DESCRIPTN;
_FIELD_TEXT = objEntr.FIELD_TEXT;
_FROM_TEXT = objEntr.FROM_TEXT;
_DAG = objEntr.DAG;
_SCHEMA_STATUS = objEntr.SCHEMA_STATUS;
_UPDATED = objEntr.UPDATED;
_ENTER_BY = objEntr.ENTER_BY;
_DEFAULT_CONN = objEntr.DEFAULT_CONN;
}
public string InsertUpdate(CoreQD_SCHEMAInfo obj)
{
string sErr = "";
if (IsExist(
obj.CONN_ID,
obj.SCHEMA_ID
))
{
sErr = Update(obj);
}
else
{
Add(obj, ref sErr);
}
return(sErr);
}
/// <summary>
/// Return 1: Table is exist Identity Field
/// Return 0: Table is not exist Identity Field
/// Return -1: Erro
/// </summary>
/// <param name="tableName"></param>
public Int32 Add(CoreQD_SCHEMAInfo objEntr, ref string sErr)
{
int ret = -1; try
{
connect();
InitSPCommand(_strSPInsertName);
AddParameter(CoreQD_SCHEMAInfo.Field.CONN_ID.ToString(), objEntr.CONN_ID);
AddParameter(CoreQD_SCHEMAInfo.Field.SCHEMA_ID.ToString(), objEntr.SCHEMA_ID);
AddParameter(CoreQD_SCHEMAInfo.Field.LOOK_UP.ToString(), objEntr.LOOK_UP);
AddParameter(CoreQD_SCHEMAInfo.Field.DESCRIPTN.ToString(), objEntr.DESCRIPTN);
AddParameter(CoreQD_SCHEMAInfo.Field.FIELD_TEXT.ToString(), objEntr.FIELD_TEXT);
AddParameter(CoreQD_SCHEMAInfo.Field.FROM_TEXT.ToString(), objEntr.FROM_TEXT);
AddParameter(CoreQD_SCHEMAInfo.Field.DAG.ToString(), objEntr.DAG);
AddParameter(CoreQD_SCHEMAInfo.Field.SCHEMA_STATUS.ToString(), objEntr.SCHEMA_STATUS);
AddParameter(CoreQD_SCHEMAInfo.Field.UPDATED.ToString(), objEntr.UPDATED);
AddParameter(CoreQD_SCHEMAInfo.Field.ENTER_BY.ToString(), objEntr.ENTER_BY);
AddParameter(CoreQD_SCHEMAInfo.Field.DEFAULT_CONN.ToString(), objEntr.DEFAULT_CONN);
//command.ExecuteNonQuery();
object tmp = executeSPScalar();
if (tmp != null && tmp != DBNull.Value)
{
ret = Convert.ToInt32(tmp);
}
else
{
ret = 0;
}
}
catch (Exception ex)
{
sErr = ex.Message;
}
disconnect();
if (sErr != "")
{
CoreErrorLog.SetLog(sErr);
}
return(ret);
}
public CoreQD_SCHEMAInfo Get(
String CONN_ID,
String SCHEMA_ID,
ref string sErr)
{
CoreQD_SCHEMAInfo objEntr = new CoreQD_SCHEMAInfo(); DataTable list = new DataTable();
try
{
connect();
InitSPCommand(_strSPGetName);
AddParameter(CoreQD_SCHEMAInfo.Field.CONN_ID.ToString(), CONN_ID);
AddParameter(CoreQD_SCHEMAInfo.Field.SCHEMA_ID.ToString(), SCHEMA_ID);
list = executeSelectSP(command);
}
catch (Exception ex)
{
sErr = ex.Message;
}
finally
{
disconnect();
}
if (list.Rows.Count > 0)
{
objEntr = new CoreQD_SCHEMAInfo(list.Rows[0]);
}
//if (dr != null) list = CBO.FillCollection(dr, ref list);
if (sErr != "")
{
CoreErrorLog.SetLog(sErr);
}
return(objEntr);
}
private string CreateWhereClause(CoreQD_SCHEMAInfo obj)
{
String result = "";
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);
}
public DataTableCollection Get_Page(CoreQD_SCHEMAInfo obj, string orderBy, int pageIndex, int pageSize, ref String sErr)
{
return(_objDAO.Get_Page(obj, orderBy, pageIndex, pageSize, ref sErr));
}
public string Update(CoreQD_SCHEMAInfo obj)
{
return(_objDAO.Update(obj));
}
public Int32 Add(CoreQD_SCHEMAInfo obj, ref string sErr)
{
return(_objDAO.Add(obj, ref sErr));
}
public string TransferIn(DataRow row)
{
CoreQD_SCHEMAInfo inf = new CoreQD_SCHEMAInfo(row);
return(InsertUpdate(inf));
}
public DataTable ToTransferInStruct()
{
CoreQD_SCHEMAInfo inf = new CoreQD_SCHEMAInfo();
return(inf.ToDataTable());
}
/// <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;
}
