} // doIt
/// <summary>
/// Process Translation Table
/// </summary>
/// <param name="table">table</param>
/// <param name="AD_Client_ID">AD_Client_ID</param>
private void ProcessTable(MTable table, int AD_Client_ID)
{
StringBuilder sql = new StringBuilder();
MColumn[] columns = table.GetColumns(false);
for (int i = 0; i < columns.Length; i++)
{
MColumn column = columns[i];
if (column.GetAD_Reference_ID() == DisplayType.String ||
column.GetAD_Reference_ID() == DisplayType.Text)
{
String columnName = column.GetColumnName();
if (sql.Length != 0)
{
sql.Append(",");
}
sql.Append(columnName);
}
}
String baseTable = table.GetTableName();
baseTable = baseTable.Substring(0, baseTable.Length - 4);
log.Config(baseTable + ": " + sql);
String columnNames = sql.ToString();
sql = new StringBuilder();
sql.Append("UPDATE ").Append(table.GetTableName()).Append(" t SET (")
.Append(columnNames).Append(") = (SELECT ").Append(columnNames)
.Append(" FROM ").Append(baseTable).Append(" b WHERE t.")
.Append(baseTable).Append("_ID=b.").Append(baseTable).Append("_ID) WHERE AD_Client_ID=")
.Append(AD_Client_ID);
int no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_Trx());
AddLog(0, null, new Decimal(no), baseTable);
} // processTable
/// <summary>
/// Process logic to verify database columns and Columns in Application Dictionary
/// </summary>
/// <returns></returns>
protected override string DoIt()
{
// if table not found, in case if run from menu
// or from table, then return with message
if (p_AD_Table_ID.Trim() == "")
{
return(Msg.GetMsg(GetCtx(), "VIS_TableNotSelected"));
}
DataSet dsDT = DB.ExecuteDataset("SELECT AD_Reference_ID, Name FROM AD_Reference WHERE IsActive = 'Y' AND ValidationType = 'D'");
string[] tableIDs = p_AD_Table_ID.Split(',');
// loop through tables selected in parameter or
// if run from window then single table ID
for (int i = 0; i < tableIDs.Length; i++)
{
int AD_Table_ID = Util.GetValueOfInt(tableIDs[i]);
MTable table = MTable.Get(GetCtx(), AD_Table_ID);
// Check on table whether it has single or multiple keys
bool hasSingleKey = true;
if (!table.IsSingleKey())
{
hasSingleKey = false;
}
if (table == null || table.Get_ID() == 0)
{
return(Msg.GetMsg(GetCtx(), "VIS_TableNotFound"));
}
// create HTML for tables
sbHTML.Append("<div class='vis-val-tc-parCtr'> "
+ "<div class='vis-val-tc-hdr'><label>" + Msg.Translate(GetCtx(), "AD_Table_ID") + ": </label>" + table.GetTableName() + "</div>"
+ "<div class='vis-val-tc-colCtr'>"
+ "<div class='vis-val-tc-colHdrs'>" + Msg.GetMsg(GetCtx(), "VIS_ADCols") + "</div>"
+ "<div class='vis-val-tc-colHdrs'>" + Msg.GetMsg(GetCtx(), "VIS_DBCols") + "</div>"
+ "</div>");
// Find Column in Database
DatabaseMetaData md = new DatabaseMetaData();
String catalog = "";
String schema = DataBase.DB.GetSchema();
//get table name
string tableName = table.GetTableName();
//get columns of a table
DataSet dt = md.GetColumns(catalog, schema, tableName);
bool hasDBCols = false;
if (dt.Tables[0].Rows.Count > 0)
{
hasDBCols = true;
}
// get all columns from table
MColumn[] columnsAD = table.GetColumns(true);
// variables to create HTML as string for both AD and in DB
StringBuilder adColName = new StringBuilder("");
StringBuilder dbColName = new StringBuilder("");
List <string> ADCols = new List <string>();
// check whether process is running from menu or from table and column Window
// if process is executing from window then apply style
string mainMenuWrap = "";
if (!fromMenu)
{
mainMenuWrap = "style='max-height: 450px;'";
}
sbHTML.Append("<div class='vis-val-tc-mainwrap' " + mainMenuWrap + "> ");
if (columnsAD.Length > 0)
{
// loop through Columns present in Application dictionary
for (int c = 0; c < columnsAD.Length; c++)
{
var col = columnsAD[c];
adColName.Clear();
adColName.Append(col.GetColumnName().ToUpper());
DataRow[] dr = null;
if (hasDBCols && dt.Tables[0].Rows.Count > 0)
{
dr = dt.Tables[0].Select("COLUMN_NAME = '" + adColName.ToString() + "'");
}
DataRow[] drRef = dsDT.Tables[0].Select("AD_Reference_ID = " + col.GetAD_Reference_ID());
// if column is virtual, then add style
var style = " color: red; font-style: italic;";
if (col.IsVirtualColumn() || (dr != null && dr.Length > 0))
{
style = " color: green;";
}
// add different style for key column
string keyCol = "";
// Condition for Multikey columns and Single key to mark as Primary key columns
if ((hasSingleKey && col.GetAD_Reference_ID() == 13) || (!hasSingleKey && col.IsParent()))
{
keyCol = " * ";
style += " font-weight: bold; font-size: initial;";
}
sbHTML.Append("<div class='vis-val-tc-colWid'>"
+ "<div class='vis-val-tc-col-l' style='" + style + "'> " + keyCol + " " + col.GetColumnName() + " (" + drRef[0]["Name"] + " (" + col.GetFieldLength() + ") " + " ) " + "</div>");
if (dr != null && dr.Length > 0)
{
sbHTML.Append("<div class='vis-val-tc-col-r' style='" + style + "'>" + Util.GetValueOfString(dr[0]["Column_Name"]) + " (" + Util.GetValueOfString(dr[0]["DATATYPE"]) + " (" + Util.GetValueOfInt(dr[0]["LENGTH"]) + ") " + " ) " + "</div>");
}
else
{
if (col.IsVirtualColumn())
{
sbHTML.Append("<div class='vis-val-tc-virCol' style='" + style + "'>" + Msg.GetMsg(GetCtx(), "VIS_VirtualCol") + "</div>");
}
else
{
sbHTML.Append("<div class='vis-val-tc-col-r' style='" + style + "'>" + Msg.GetMsg(GetCtx(), "VIS_DBNotFound") + "</div>");
}
}
sbHTML.Append("</div>");
ADCols.Add(adColName.ToString());
}
}
// if columns present in database
if (hasDBCols)
{
// loop through columns in DB
for (int d = 0; d < dt.Tables[0].Rows.Count; d++)
{
dbColName.Clear();
dbColName.Append(dt.Tables[0].Rows[d]["Column_Name"]);
if (ADCols.Contains(dbColName.ToString()))
{
continue;
}
else
{
sbHTML.Append("<div class='vis-val-tc-colWid'>");
DataRow dr = null;
if (hasDBCols && dt.Tables[0].Rows.Count > 0)
{
dr = dt.Tables[0].Rows[d];
}
if (dr != null)
{
sbHTML.Append("<div class='vis-val-tc-col-red-r'>" + Util.GetValueOfString(dr["Column_Name"]) + " (" + Util.GetValueOfString(dr["DATATYPE"]) + " (" + Util.GetValueOfInt(dr["LENGTH"]) + ") " + " ) " + "</div>");
}
else
{
sbHTML.Append("<div class='vis-val-tc-col-red-r'>" + Msg.GetMsg(GetCtx(), "VIS_ADNotFound") + "</div>");
}
sbHTML.Append("<div class='vis-val-tc-col-red-l'>" + Msg.GetMsg(GetCtx(), "VIS_ADNotFound") + "</div>");
sbHTML.Append("</div>");
}
}
}
sbHTML.Append("</div>");
sbHTML.Append("</div>");
md.Dispose();
ProcessInfo pi = GetProcessInfo();
pi.SetCustomHTML(sbHTML.ToString());
}
return("");
}
/// <summary>
/// Gets the parent record for every row (if exists)
/// </summary>
/// <param name="currentTable">Current table to be parsed</param>
/// <param name="exportdata">Exportdata info</param>
private string GetForeignData(MTable currentTable, ExportDataRecords exportdata)
{
try
{
//check if the record is already exported.
var res = _ExecutedRecordList.Where((a) => a.AD_Table_ID == exportdata.AD_Table_ID)
.Where((a) => a.Record_ID == exportdata.Record_ID)
.Where((a) => a.AD_ColOne_ID == exportdata.AD_ColOne_ID);
//if (res.Count() <= 0)
{
_ExecutedRecordList.Add(exportdata);
String tableName = currentTable.GetTableName();
if (!_ExceptionTables.Contains(tableName))
{
if (exportdata.AD_ColOne_ID == 0)
{
File.AppendAllText(HostingEnvironment.ApplicationPhysicalPath + "\\log\\XMLLog.txt", tableName + " : " + exportdata.Record_ID);
int found = 0;
if (ds.Tables[tableName] != null)
{
found = ds.Tables[tableName].Select(tableName + "_ID = " + exportdata.Record_ID).Count();
}
MColumn[] columns = currentTable.GetColumns(true); //Fetch column details
if (columns.Length > 0)
{
string sql = GetSql(currentTable.GetAD_Table_ID(), currentTable.GetTableName(), exportdata);
DataSet tmpDS = DB.ExecuteDataset(sql, null);
if (tmpDS == null || tmpDS.Tables[0].Rows.Count <= 0)
{
//sql = sql.Substring(sql.IndexOf("WHERE"));
if (tmpDS != null)
{
deleteSqlExp.Add("delete from AD_ExportData Where record_ID = " + exportdata.Record_ID + " and ad_table_id = " + exportdata.AD_Table_ID + " and ad_Moduleinfo_id = " + _AD_ModuleInfo_ID);
}
return("");
}
if (tmpDS.Tables[0].Rows[0]["Export_ID"].Equals(DBNull.Value))
{
tmpDS.Tables[0].Rows[0]["Export_ID"] = ManageExportID(exportdata.Record_ID, tableName);
}
ds.AddOrCopy(tmpDS, tableName, exportdata.Record_ID, 0, null, rowNum++); //add or copy
for (int cols = 0; cols <= columns.Length - 1; cols++)
{
string colName = columns[cols].GetColumnName();
int refVID = columns[cols].GetAD_Reference_Value_ID();
int refID = columns[cols].GetAD_Reference_ID();
// Special case applied for workflow table to bypass the start node on workflow- asked by mukesh sir- done by mohit- 1 February 2019.
if (tableName == "AD_Workflow" && colName == "AD_WF_Node_ID")
{
continue;
}
if (!columns[cols].IsStandardColumn() && !columns[cols].IsKey())
{
if (colName.EndsWith("_ID")) //only columns ending with _ID to be processed (indicated Foreign Key )
{
if (!columns[cols].GetColumnName().Equals("Export_ID"))
{
Object colValue = tmpDS.Tables[0].Rows[0][colName];
if (colValue != null)
{
if (!String.IsNullOrEmpty(colValue.ToString()))
{
MTable fkTable = columns[cols].GetFKTable(); //Get the Parent table of the FK Column
if (fkTable != null)
{
GetForeignData(fkTable, new ExportDataRecords()
{
AD_Table_ID = fkTable.GetAD_Table_ID(), Record_ID = Convert.ToInt32(colValue)
});
}
}
}
}
}
else if (refID == DisplayType.List || refID == DisplayType.Table)
{
Object colValue = tmpDS.Tables[0].Rows[0][colName];
MRefTable refTable = CheckReference(new ExportDataRecords()
{
Record_ID = refVID, AD_Table_ID = MTable.Get_Table_ID("AD_Reference")
}, "AD_Reference");
if (refTable != null && colValue != null && colValue.ToString() != "")
{
try
{
MTable tbl = MTable.Get(GetCtx(), refTable.GetAD_Table_ID());
//string tName = MTable.GetTableName(GetCtx(), refTable.GetAD_Table_ID());
string cName = MColumn.GetColumnName(GetCtx(), refTable.GetColumn_Key_ID());
int recordId;
if (int.TryParse(colValue.ToString(), out recordId)) //If Value is type of int
{
;
}
else
{
recordId = Convert.ToInt32(DB.ExecuteScalar("SELECT " + tbl.GetTableName() + "_ID FROM " + tbl.GetTableName() + " WHERE " + cName + " = '" + colValue.ToString() + "'"));
cName = tbl.GetTableName() + "_ID";
}
DataSet temp = DB.ExecuteDataset("SELECT * FROM " + tbl.GetTableName() + " WHERE " + cName + " = " + recordId);
ds.AddOrCopy(temp, tbl.GetTableName(), recordId, 0, null, rowNum++);
GetForeignData(tbl, new ExportDataRecords()
{
AD_Table_ID = tbl.GetAD_Table_ID(), Record_ID = recordId
});
}
catch (Exception ex)
{
log.Severe("Table Reference =>" + ex.Message);
}
}
}
else
{
;
}
}
}
} //column length #if
}
else
{
MColumn[] columns = currentTable.GetColumns(true); //Fetch column details
if (columns.Length > 0)
{
string sql = GetSql(currentTable.GetAD_Table_ID(), currentTable.GetTableName(), exportdata);
DataSet tmpDS = DB.ExecuteDataset(sql, null);
if (tmpDS == null || tmpDS.Tables[0].Rows.Count <= 0)
{
//sql = sql.Substring(sql.IndexOf("WHERE"));
if (tmpDS != null)
{
deleteSqlExp.Add("delete from AD_ExportData Where record_ID = " + exportdata.Record_ID + " and ad_table_id = " + exportdata.AD_Table_ID + " and ad_Moduleinfo_id = " + _AD_ModuleInfo_ID);
}
return("");
}
if (tmpDS.Tables[0].Rows[0]["Export_ID"].Equals(DBNull.Value))
{
tmpDS.Tables[0].Rows[0]["Export_ID"] = ManageExportID(exportdata.Record_ID, exportdata.AD_ColOne_ID, tableName, currentTable.GetAD_Table_ID());
}
ds.AddOrCopy(tmpDS, tableName, exportdata.Record_ID, exportdata.AD_ColOne_ID, GetParentColumns(currentTable.GetAD_Table_ID()), rowNum++); //add or copy
for (int cols = 0; cols <= columns.Length - 1; cols++)
{
string colName = columns[cols].GetColumnName();
int refVID = columns[cols].GetAD_Reference_Value_ID();
int refID = columns[cols].GetAD_Reference_ID();
if (!columns[cols].IsStandardColumn() && !columns[cols].IsKey())
{
if (colName.EndsWith("_ID")) //only columns ending with _ID to be processed (indicated Foreign Key )
{
if (!columns[cols].GetColumnName().Equals("Export_ID"))
{
Object colValue = tmpDS.Tables[0].Rows[0][colName];
if (colValue != null)
{
if (!String.IsNullOrEmpty(colValue.ToString()))
{
MTable fkTable = columns[cols].GetFKTable(); //Get the Parent table of the FK Column
if (fkTable != null)
{
GetForeignData(fkTable, new ExportDataRecords()
{
AD_Table_ID = fkTable.GetAD_Table_ID(), Record_ID = Convert.ToInt32(colValue)
});
}
}
}
}
}
else if (refID == DisplayType.List || refID == DisplayType.Table)
{
CheckReference(new ExportDataRecords()
{
Record_ID = refVID, AD_Table_ID = MTable.Get_Table_ID("AD_Reference")
}, "AD_Reference");
}
else
{
;
}
}
}
} //c
}
} //exception table #if
}
//else
//{
//}
}
catch (Exception ex)
{
log.Log(Level.SEVERE, ex.Message);
return(ex.Message.ToString());
}
return("");
}
