/// <summary>
/// Gets the Records to be exported into Xml
/// </summary>
/// <returns>Records in list</returns>
List <ExportDataRecords> GetExportData()
{
List <ExportDataRecords> list = new List <ExportDataRecords>();
String _sql = "SELECT AD_Table_ID, Record_ID,AD_ColOne_ID FROM AD_ExportData WHERE IsActive = 'Y' AND AD_ModuleInfo_ID = @AD_ModuleInfo_ID ";
SqlParameter[] param = new SqlParameter[1];
param[0] = new SqlParameter("@AD_ModuleInfo_ID", _AD_ModuleInfo_ID);
IDataReader dr = DB.ExecuteReader(_sql, param);
while (dr.Read())
{
ExportDataRecords records = new ExportDataRecords();
records.AD_Table_ID = int.Parse(dr["AD_Table_ID"].ToString());
records.Record_ID = int.Parse(dr["Record_ID"].ToString());
if (dr["AD_ColOne_ID"] != DBNull.Value && dr["AD_ColOne_ID"] != null)
{
records.AD_ColOne_ID = Convert.ToInt32(dr["AD_ColOne_ID"]);
}
else
{
records.AD_ColOne_ID = 0;
}
list.Add(records);
}
dr.Close();
return(list);
}
String GetSql(int _TableID, string _tableName, ExportDataRecords export)
{
String _Sql = "";
try
{
string[] ds = GetParentColumns(_TableID);
// Code Commented by Lokesh Chauhan to get All Records for Marking those which are inactive too
// _Sql = "SELECT * FROM " + _tableName + " WHERE IsActive = 'Y' AND ";
_Sql = "SELECT * FROM " + _tableName + " WHERE ";
//_Sql += ds.Tables[0].Rows[0]["ColumnName"].ToString() + " =" + export.Record_ID;
_Sql += ds[0] + " =" + export.Record_ID;
if (ds.Length > 1)
{
// _Sql += " and " + ds.Tables[0].Rows[1]["ColumnName"].ToString() + " =" + export.AD_ColOne_ID;
_Sql += " and " + ds[1] + " =" + export.AD_ColOne_ID;
}
}
catch
{
}
return(_Sql);
}
/// <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("");
}
/// <summary>
/// Check for Reference List in database
/// </summary>
/// <param name="refVID">Ref value ID</param>
/// <param name="tableName">Name of the table</param>
MRefTable CheckReference(ExportDataRecords refRecord, String _tableName)
{
MRefTable refTable = null;
int refVID = refRecord.Record_ID;
string tableName = _tableName;
//check if the record is already exported.
var res = _ExecutedRecordList.Where((a) => a.AD_Table_ID == refRecord.AD_Table_ID)
.Where((a) => a.Record_ID == refRecord.Record_ID);
//if (res.Count() <= 0)
{
_ExecutedRecordList.Add(refRecord);
String chkRef = "SELECT * FROM AD_Reference WHERE IsActive = 'Y' AND AD_Reference_ID =" + refVID;
String ValidationType = "";
DataSet tmpDS = DB.ExecuteDataset(chkRef);
if (tmpDS.Tables[0].Rows[0]["ValidationType"] != DBNull.Value)
{
ValidationType = tmpDS.Tables[0].Rows[0]["ValidationType"].ToString();
}
tmpDS.Tables[0].TableName = tableName;
if (tmpDS.Tables[0].Rows[0]["Export_ID"].Equals(DBNull.Value))
{
tmpDS.Tables[0].Rows[0]["Export_ID"] = ManageExportID(refVID, tableName);
}
DataSet tempRefDS = tmpDS.Copy();
if (!String.IsNullOrEmpty(ValidationType)) //if reference entry is found, chck furhter for ad_ref_list records
{
String refTableName = ValidationType.Equals("L") ? "AD_Ref_List" : "AD_Ref_Table";
tmpDS = DB.ExecuteDataset(GetSql(refTableName, refVID, "AD_Reference_ID"));
tmpDS.Tables[0].TableName = refTableName;
for (int a = 0; a < tmpDS.Tables[0].Rows.Count; a++)
{
try
{
String tempStore = "";
int PrimaryID = 0;
if (ValidationType.Equals("T"))
{
tempStore = "AD_Reference_ID";
PrimaryID = int.Parse(tmpDS.Tables[0].Rows[a][tempStore].ToString());
refTable = new MRefTable(GetCtx(), tmpDS.Tables[0].Rows[a], null);
if (tmpDS.Tables[0].Rows[a]["Export_ID"].Equals(DBNull.Value))
{
tmpDS.Tables[0].Rows[a]["Export_ID"] = ManageExportID(refVID, tableName, "AD_Ref_Table");
}
}
else
{
PrimaryID = int.Parse(tmpDS.Tables[0].Rows[a][refTableName + "_ID"].ToString());
if (tmpDS.Tables[0].Rows[a]["Export_ID"].Equals(DBNull.Value))
{
tmpDS.Tables[0].Rows[a]["Export_ID"] = ManageExportID(PrimaryID, refTableName);
}
ds.AddOrCopy(tmpDS, refTableName, PrimaryID, 0, null, rowNum++);
}
}
catch
{
log.Log(Level.SEVERE, "PrimaryID Issue");
}
}
if (ValidationType.Equals("T"))
{
if (tmpDS != null && tmpDS.Tables[0].Rows.Count > 0)
{
// ds.AddOrCopy(tmpDS, refTableName, refVID, 0, null, rowNum++);
}
}
}
ds.AddOrCopy(tempRefDS, tableName, refVID, 0, null, rowNum++);
return(refTable);
}
}
