} // createStatement
/// <summary>
/// Get SQL Value
/// </summary>
/// <param name="value">string value</param>
/// <returns>sql compliant value</returns>
private String GetSQLValue(String value)
{
if (value == null || value.Length == 0 || value.Equals("NULL"))
{
return("NULL");
}
// Data Types
if (DisplayType.IsNumeric(_column.GetAD_Reference_ID()) ||
DisplayType.IsID(_column.GetAD_Reference_ID()))
{
return(value);
}
if (DisplayType.YesNo == _column.GetAD_Reference_ID())
{
if (value.Equals("true"))
{
return("'Y'");
}
else
{
return("'N'");
}
}
if (DisplayType.IsDate(_column.GetAD_Reference_ID()))
{
return(DataBase.DB.TO_DATE(Convert.ToDateTime(value)));
}
// String, etc.
return(DataBase.DB.TO_STRING(value));
} // getSQLValue
} // 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>
/// Parse Variable
/// </summary>
/// <param name="variable">variable</param>
/// <param name="po">po object</param>
/// <returns>translated variable or if not found the original tag</returns>
private static string ParseVariable(String variable, PO po)
{
int index = po.Get_ColumnIndex(variable);
if (index == -1)
{
return("@" + variable + "@"); // keep for next
}
//
Object value = po.Get_Value(index);
if (value == null)
{
return("");
}
POInfo _poInfo = POInfo.GetPOInfo(po.GetCtx(), po.Get_Table_ID());
MColumn column = (new MTable(po.GetCtx(), po.Get_Table_ID(), null)).GetColumn(variable);
if (column.GetAD_Reference_ID() == DisplayType.Location)
{
StringBuilder sb = new StringBuilder();
DataSet ds = DB.ExecuteDataset(@"SELECT l.address1,
l.address2,
l.address3,
l.address4,
l.city,
CASE
WHEN l.C_City_ID IS NOT NULL
THEN
( SELECT NAME FROM C_City ct WHERE ct.C_City_ID=l.C_City_ID
)
ELSE NULL
END CityName,
(SELECT NAME FROM C_Country c WHERE c.C_Country_ID=l.C_Country_ID
) AS CountryName
FROM C_Location l WHERE l.C_Location_ID=" + value);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
if (ds.Tables[0].Rows[0]["address1"] != null && ds.Tables[0].Rows[0]["address1"] != DBNull.Value)
{
sb.Append(ds.Tables[0].Rows[0]["address1"]).Append(",");
}
if (ds.Tables[0].Rows[0]["address2"] != null && ds.Tables[0].Rows[0]["address2"] != DBNull.Value)
{
sb.Append(ds.Tables[0].Rows[0]["address2"]).Append(",");
}
if (ds.Tables[0].Rows[0]["address3"] != null && ds.Tables[0].Rows[0]["address3"] != DBNull.Value)
{
sb.Append(ds.Tables[0].Rows[0]["address3"]).Append(",");
}
if (ds.Tables[0].Rows[0]["address4"] != null && ds.Tables[0].Rows[0]["address4"] != DBNull.Value)
{
sb.Append(ds.Tables[0].Rows[0]["address4"]).Append(",");
}
if (ds.Tables[0].Rows[0]["city"] != null && ds.Tables[0].Rows[0]["city"] != DBNull.Value)
{
sb.Append(ds.Tables[0].Rows[0]["city"]).Append(",");
}
if (ds.Tables[0].Rows[0]["CityName"] != null && ds.Tables[0].Rows[0]["CityName"] != DBNull.Value)
{
sb.Append(ds.Tables[0].Rows[0]["CityName"]).Append(",");
}
if (ds.Tables[0].Rows[0]["CountryName"] != null && ds.Tables[0].Rows[0]["CountryName"] != DBNull.Value)
{
sb.Append(ds.Tables[0].Rows[0]["CountryName"]).Append(",");
}
return(sb.ToString().TrimEnd(','));
}
else
{
return("");
}
}
//Get lookup display column name for ID
if (_poInfo != null && _poInfo.getAD_Table_ID() == po.Get_Table_ID() && _poInfo.IsColumnLookup(index) && value != null)
{
VLookUpInfo lookup = _poInfo.GetColumnLookupInfo(index); //create lookup info for column
DataSet ds = DB.ExecuteDataset(lookup.queryDirect.Replace("@key", DB.TO_STRING(value.ToString())), null); //Get Name from data
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
value = ds.Tables[0].Rows[0][2]; //Name Value
}
}
if (column.GetAD_Reference_ID() == DisplayType.Date)
{
return(Util.GetValueOfDateTime(value).Value.Date.ToShortDateString());
}
return(value.ToString());
}
} // doIt
private String createFK()
{
String returnMessage = "";
if (p_AD_Column_ID == 0)
{
throw new Exception("@No@ @AD_Column_ID@");
}
MColumn column = new MColumn(GetCtx(), p_AD_Column_ID, Get_Trx());
if (column.Get_ID() == 0)
{
throw new Exception("@NotFound@ @AD_Column_ID@ " + p_AD_Column_ID);
}
MTable table = MTable.Get(GetCtx(), column.GetAD_Table_ID());
if (table.Get_ID() == 0)
{
throw new Exception("@NotFound@ @AD_Table_ID@ " + column.GetAD_Table_ID());
}
String fk;
if ((column.GetAD_Reference_ID() == DisplayType.Account) &&
!(column.GetColumnName().Equals("C_ValidCombination_ID", StringComparison.OrdinalIgnoreCase)))
{
fk = "SELECT t.TableName, c.ColumnName, c.AD_Column_ID,"
+ " cPK.AD_Column_ID, tPK.TableName, cPK.ColumnName, c.ConstraintType,"
+ " 'FK' || t.AD_Table_ID || '_' || c.AD_Column_ID AS ConstraintName "
+ "FROM AD_Table t"
+ " INNER JOIN AD_Column c ON (t.AD_Table_ID=c.AD_Table_ID)"
+ " INNER JOIN AD_Column cPK ON (cPK.AD_Column_ID=1014)"
+ " INNER JOIN AD_Table tPK ON (cPK.AD_Table_ID=tPK.AD_Table_ID) "
+ "WHERE c.IsKey='N' AND c.AD_Reference_ID=25 AND C.AD_Column_ID= @param" // Acct
+ " AND c.ColumnName<>'C_ValidCombination_ID'"
+ " AND t.IsView='N' "
+ " ORDER BY t.TableName, c.ColumnName";
}
else if ((column.GetAD_Reference_ID() == DisplayType.PAttribute) &&
!(column.GetColumnName().Equals("C_ValidCombination_ID", StringComparison.OrdinalIgnoreCase)))
{
fk = "SELECT t.TableName, c.ColumnName, c.AD_Column_ID,"
+ " cPK.AD_Column_ID, tPK.TableName, cPK.ColumnName, c.ConstraintType,"
+ " 'FK' || t.AD_Table_ID || '_' || c.AD_Column_ID AS ConstraintName "
+ "FROM AD_Table t"
+ " INNER JOIN AD_Column c ON (t.AD_Table_ID=c.AD_Table_ID)"
+ " INNER JOIN AD_Column cPK ON (cPK.AD_Column_ID=8472)"
+ " INNER JOIN AD_Table tPK ON (cPK.AD_Table_ID=tPK.AD_Table_ID) "
+ "WHERE c.IsKey='N' AND c.AD_Reference_ID=35 AND C.AD_Column_ID=@param" // Product Attribute
+ " AND c.ColumnName<>'C_ValidCombination_ID'"
+ " AND t.IsView='N' "
+ " ORDER BY t.TableName, c.ColumnName";
}
else if (((column.GetAD_Reference_ID() == DisplayType.TableDir) || (column.GetAD_Reference_ID() == DisplayType.Search)) &&
(column.GetAD_Reference_Value_ID() == 0))
{
fk = "SELECT t.TableName, c.ColumnName, c.AD_Column_ID,"
+ " cPK.AD_Column_ID, tPK.TableName, cPK.ColumnName, c.ConstraintType,"
+ " 'FK' || t.AD_Table_ID || '_' || c.AD_Column_ID AS ConstraintName "
+ "FROM AD_Table t"
+ " INNER JOIN AD_Column c ON (t.AD_Table_ID=c.AD_Table_ID)"
+ " INNER JOIN AD_Column cPK ON (c.AD_Element_ID=cPK.AD_Element_ID AND cPK.IsKey='Y')"
+ " INNER JOIN AD_Table tPK ON (cPK.AD_Table_ID=tPK.AD_Table_ID AND tPK.IsView='N') "
+ "WHERE c.IsKey='N' AND c.AD_Reference_Value_ID IS NULL AND C.AD_Column_ID=@param"
+ " AND t.IsView='N' AND c.ColumnSQL IS NULL "
+ " ORDER BY t.TableName, c.ColumnName";
}
else // Table
{
fk = "SELECT t.TableName, c.ColumnName, c.AD_Column_ID,"
+ " cPK.AD_Column_ID, tPK.TableName, cPK.ColumnName, c.ConstraintType,"
+ " 'FK' || t.AD_Table_ID || '_' || c.AD_Column_ID AS ConstraintName "
+ "FROM AD_Table t"
+ " INNER JOIN AD_Column c ON (t.AD_Table_ID=c.AD_Table_ID AND c.AD_Reference_Value_ID IS NOT NULL)"
+ " INNER JOIN AD_Ref_Table rt ON (c.AD_Reference_Value_ID=rt.AD_Reference_ID)"
+ " INNER JOIN AD_Column cPK ON (rt.Column_Key_ID=cPK.AD_Column_ID)"
+ " INNER JOIN AD_Table tPK ON (cPK.AD_Table_ID=tPK.AD_Table_ID) "
+ "WHERE c.IsKey='N'"
+ " AND t.IsView='N' AND c.ColumnSQL IS NULL AND C.AD_Column_ID=@param"
+ " ORDER BY t.TableName, c.ColumnName";
}
SqlParameter[] pstmt = null;
DataSet ds = null;
try
{
/*Find foreign key relation in Database
* */
//Trx trx = Trx.Get("getDatabaseMetaData");
DatabaseMetaData md = new DatabaseMetaData();
String catalog = "";// DB.getCatalog();
String schema = DB.GetSchema();
String tableName = table.GetTableName();
String dropsql = null;
int no = 0;
String constraintNameDB = null;
String PKTableNameDB = null;
String PKColumnNameDB = null;
int constraintTypeDB = 0;
/* Get foreign key information from DatabaseMetadata
* */
ds = md.GetForeignKeys(catalog, schema, tableName);
md.Dispose();
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
Dictionary <String, String> fkcolumnDetail = md.GetForeignColumnDetail(ds.Tables[0].Rows[i]);
// string sql = "SELECT column_name FROM user_cons_columns WHERE constraint_name='" + ds.Tables[0].Rows[i]["FOREIGN_KEY_CONSTRAINT_NAME"].ToString() + "'";
//string fkcolumnName = Util.GetValueOfString(DB.ExecuteScalar(sql));
if (fkcolumnDetail["FK_Column_Name"].Equals(column.GetColumnName(), StringComparison.OrdinalIgnoreCase))
{
constraintNameDB = fkcolumnDetail["ConstraintNameDB"];
PKTableNameDB = fkcolumnDetail["PK_Table_Name"]; //rs.GetString("PKTABLE_NAME");
PKColumnNameDB = fkcolumnDetail["PK_Column_Name"]; //rs.GetString("PKCOLUMN_NAME");
constraintTypeDB = md.GetConstraintTypeDB(fkcolumnDetail["Delete_Rule"]); //rs.getShort("DELETE_RULE");
break;
}
}
}
pstmt = new SqlParameter[1];
pstmt[0] = new SqlParameter("@param", column.Get_ID());
ds = DB.ExecuteDataset(fk, pstmt, Get_Trx());
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
String TableName = ds.Tables[0].Rows[0]["TableName"].ToString();
String ColumnName = ds.Tables[0].Rows[0]["ColumnName"].ToString();
// int AD_Column_ID = rs.getInt (3);
// int PK_Column_ID = rs.getInt (4);
String PKTableName = ds.Tables[0].Rows[0]["TableName1"].ToString();
String PKColumnName = ds.Tables[0].Rows[0]["ColumnName1"].ToString();
String ConstraintType = ds.Tables[0].Rows[0]["ConstraintType"].ToString();
String ConstraintName = ds.Tables[0].Rows[0]["ConstraintName"].ToString();
/* verify if the constraint in DB is different than the one to be created */
Boolean modified = true;
if (constraintNameDB != null)
{
if (((constraintNameDB.Equals(ConstraintName, StringComparison.OrdinalIgnoreCase)) &&
(PKTableNameDB != null) && (PKTableNameDB.Equals(PKTableName, StringComparison.OrdinalIgnoreCase)) &&
(PKColumnNameDB != null) && (PKColumnNameDB.Equals(PKColumnName, StringComparison.OrdinalIgnoreCase)) &&
((constraintTypeDB == DatabaseMetaData.importedKeyRestrict) &&
(X_AD_Column.CONSTRAINTTYPE_Restrict.Equals(ConstraintType) ||
X_AD_Column.CONSTRAINTTYPE_RistrictTrigger.Equals(ConstraintType))))
||
((constraintTypeDB == DatabaseMetaData.importedKeyCascade) &&
(X_AD_Column.CONSTRAINTTYPE_Cascade.Equals(ConstraintType) ||
X_AD_Column.CONSTRAINTTYPE_CascadeTrigger.Equals(ConstraintType)))
||
((constraintTypeDB == DatabaseMetaData.importedKeySetNull) &&
(X_AD_Column.CONSTRAINTTYPE_Null.Equals(ConstraintType)))
)
{
modified = false;
}
else
{
dropsql = "ALTER TABLE " + table.GetTableName()
+ " DROP CONSTRAINT " + constraintNameDB;
}
}
if (modified)
{
StringBuilder sql = null;
try
{
if (dropsql != null)
{
/* Drop the existing constraint */
//no = DB.executeUpdate(Get_Trx(), dropsql);
no = DB.ExecuteQuery(dropsql, null, Get_Trx());
AddLog(0, null, Decimal.Parse(no.ToString()), dropsql);
}
/* Now create the sql foreign key constraint */
sql = new StringBuilder("ALTER TABLE ")
.Append(TableName)
.Append(" ADD CONSTRAINT ").Append(ConstraintName)
.Append(" FOREIGN KEY (").Append(ColumnName)
.Append(") REFERENCES ").Append(PKTableName)
.Append(" (").Append(PKColumnName).Append(")");
Boolean createfk = true;
if (!String.IsNullOrEmpty(ConstraintType))
{
if (X_AD_Column.CONSTRAINTTYPE_DoNOTCreate.Equals(ConstraintType))
{
createfk = false;
}
else if (X_AD_Column.CONSTRAINTTYPE_Restrict.Equals(ConstraintType) ||
X_AD_Column.CONSTRAINTTYPE_RistrictTrigger.Equals(ConstraintType))
{
;
}
else if (X_AD_Column.CONSTRAINTTYPE_Cascade.Equals(ConstraintType) ||
X_AD_Column.CONSTRAINTTYPE_CascadeTrigger.Equals(ConstraintType))
{
sql.Append(" ON DELETE CASCADE");
}
else if (X_AD_Column.CONSTRAINTTYPE_Null.Equals(ConstraintType) ||
X_AD_Column.CONSTRAINTTYPE_NullTrigger.Equals(ConstraintType))
{
sql.Append(" ON DELETE SET NULL");
}
}
else
{
createfk = false;
}
/* Create the constraint */
if (createfk)
{
// no = DB.ExecuteQuery(sql.ToString(), null, Get_Trx());
no = DB.ExecuteQuery(sql.ToString(), null, Get_Trx(), false, true);
AddLog(0, null, Decimal.Parse(no.ToString()), sql.ToString());
if (no != -1)
{
log.Finer(ConstraintName + " - " + TableName + "." + ColumnName);
returnMessage = sql.ToString();
}
else
{
log.Info(ConstraintName + " - " + TableName + "." + ColumnName
+ " - ReturnCode=" + no);
returnMessage = "FAILED:" + sql.ToString();
}
} //if createfk
}
catch (Exception e)
{
log.Log(Level.SEVERE, sql.ToString() + " - " + e.ToString());
}
} // modified
} // rs.next
else
{
if (constraintNameDB != null && constraintNameDB.Equals("FK" + column.GetAD_Table_ID() + "_" + p_AD_Column_ID, StringComparison.OrdinalIgnoreCase))
{
dropsql = "ALTER TABLE " + table.GetTableName()
+ " DROP CONSTRAINT " + constraintNameDB;
/* Drop the existing constraint */
no = DB.ExecuteQuery(dropsql, null, Get_Trx());
AddLog(0, null, Decimal.Parse(no.ToString()), dropsql);
returnMessage = dropsql.ToString();
}
}
}
catch (Exception e)
{
log.Log(Level.SEVERE, fk, e);
}
return(returnMessage);
}
public string ApproveIt(int nodeID, int activityID, string textMsg, object forward, object answer, Ctx ctx)
{
MWFActivity activity = new MWFActivity(ctx, activityID, null);
MWFNode node = activity.GetNode();
int approvalLevel = node.GetApprovalLeval();
int AD_User_ID = ctx.GetAD_User_ID();
MColumn column = node.GetColumn();
if (forward != null) // Prefer Forward
{
int fw = int.Parse(forward.ToString());
if (fw == AD_User_ID || fw == 0)
{
return("");
}
if (!activity.ForwardTo(fw, textMsg, true))
{
return("CannotForward");
}
}
// User Choice - Answer
else if (MWFNode.ACTION_UserChoice.Equals(node.GetAction()))
{
if (column == null)
{
column = node.GetColumn();
}
// Do we have an answer?
int dt = column.GetAD_Reference_ID();
String value = null;
value = answer != null?answer.ToString() : null;
//if (dt == DisplayType.YesNo || dt == DisplayType.List || dt == DisplayType.TableDir)
if (!node.IsMultiApproval() &&
(dt == DisplayType.YesNo || dt == DisplayType.List || dt == DisplayType.TableDir))
{
if (value == null || value.Length == 0)
{
return("FillMandatory");
}
//
string res = SetUserChoice(AD_User_ID, value, dt, textMsg, activity, node);
if (res != "OK")
{
return(res);
}
}
//Genral Attribute Instance
//else if (column.GetColumnName().ToUpper().Equals("C_GENATTRIBUTESETINSTANCE_ID"))
//{
// if (attrib == null)
// {
// Dispatcher.BeginInvoke(delegate
// {
// SetBusy(false);
// ShowMessage.Error("FillMandatory", true, Msg.GetMsg(Envs.GetContext(), "Answer", true));
// //log.Config("Answer=" + value + " - " + textMsg);
// return;
// });
// return;
// }
// SetUserChoice(AD_User_ID, attrib.GetAttributeSetInstance().ToString(), 0, textMsg, activity, node);
//}
else if (forward == null && node.IsMultiApproval() && approvalLevel > 0 && answer.ToString().Equals("Y"))
{
int eventCount = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT COUNT(WFE.AD_WF_EventAudit_ID) FROM AD_WF_EventAudit WFE
INNER JOIN AD_WF_Process WFP ON (WFP.AD_WF_Process_ID=WFE.AD_WF_Process_ID)
INNER JOIN AD_WF_Activity WFA ON (WFA.AD_WF_Process_ID=WFP.AD_WF_Process_ID)
WHERE WFE.AD_WF_Node_ID=" + node.GetAD_WF_Node_ID() + " AND WFA.AD_WF_Activity_ID=" + activity.GetAD_WF_Activity_ID()));
if (eventCount < approvalLevel) //Forward Activity
{
int superVisiorID = Util.GetValueOfInt(DB.ExecuteScalar("SELECT Supervisor_ID FROM AD_User WHERE IsActive='Y' AND AD_User_ID=" + activity.GetAD_User_ID()));
if (superVisiorID == 0)//Approve
{
//SetUserConfirmation(AD_User_ID, textMsg, activity, node);
string res = SetUserChoice(AD_User_ID, value, dt, textMsg, activity, node);
if (res != "OK")
{
return(res);
}
}
else //forward
{
if (!activity.ForwardTo(superVisiorID, textMsg, true))
{
//Dispatcher.BeginInvoke(delegate
//{
// SetBusy(false);
// ShowMessage.Error("CannotForward", true);
// return;
//});
return("CannotForward");
}
}
}
else //Approve
{
//SetUserConfirmation(AD_User_ID, textMsg, activity, node);
string res = SetUserChoice(AD_User_ID, value, dt, textMsg, activity, node);
if (res != "OK")
{
return(res);
}
}
}
else
{
string res = SetUserChoice(AD_User_ID, value, dt, textMsg, activity, node);
if (res != "OK")
{
return(res);
}
}
}
// User Action
else
{
// log.Config("Action=" + node.GetAction() + " - " + textMsg);
//try
//{
// activity.SetUserConfirmation(AD_User_ID, textMsg);
//}
//catch (Exception exx)
//{
// Dispatcher.BeginInvoke(delegate
// {
// SetBusy(false);
// log.Log(Level.SEVERE, node.GetName(), exx);
// ShowMessage.Error("Error", true, exx.ToString());
// return;
// });
// return;
//}
activity.SetUserConfirmation(AD_User_ID, textMsg);
}
return("");
}
public ActivityInfo GetActivityInfo(int activityID, int nodeID, int wfProcessID, Ctx ctx)
{
ActivityInfo info = new ActivityInfo();
try
{
MWFNode node = new MWFNode(ctx, nodeID, null);
info.NodeAction = node.GetAction();
info.NodeName = node.GetName();
if (MWFNode.ACTION_UserChoice.Equals(node.GetAction()))
{
MColumn col = node.GetColumn();
info.ColID = col.GetAD_Column_ID();
info.ColReference = col.GetAD_Reference_ID();
info.ColReferenceValue = col.GetAD_Reference_Value_ID();
info.ColName = col.GetColumnName();
}
else if (MWFNode.ACTION_UserWindow.Equals(node.GetAction()))
{
info.AD_Window_ID = node.GetAD_Window_ID();
MWFActivity activity = new MWFActivity(ctx, activityID, null);
info.KeyCol = activity.GetPO().Get_TableName() + "_ID";
}
else if (MWFNode.ACTION_UserForm.Equals(node.GetAction()))
{
info.AD_Form_ID = node.GetAD_Form_ID();
}
string sql = @"SELECT node.ad_wf_node_ID,
node.Name AS NodeName,
usr.Name AS UserName,
wfea.wfstate,
wfea.TextMsg
FROM ad_wf_eventaudit wfea
INNER JOIN Ad_WF_Node node
ON (node.Ad_Wf_node_ID=wfea.AD_Wf_Node_id)
INNER JOIN AD_User usr
ON (usr.Ad_User_ID =wfea.ad_User_ID)
WHERE wfea.AD_WF_Process_ID=" + wfProcessID + @"
Order By wfea.ad_wf_eventaudit_id desc";
DataSet ds = DB.ExecuteDataset(sql);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
List <NodeInfo> nodeInfo = new List <NodeInfo>();
List <int> nodes = new List <int>();
NodeInfo ni = null;
NodeHistory nh = null;
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
if (!nodes.Contains(Util.GetValueOfInt(ds.Tables[0].Rows[i]["AD_WF_Node_ID"])))
{
ni = new NodeInfo();
ni.Name = Util.GetValueOfString(ds.Tables[0].Rows[i]["NodeName"]);
nh = new NodeHistory();
nh.State = Util.GetValueOfString(ds.Tables[0].Rows[i]["WFState"]);
nh.ApprovedBy = Util.GetValueOfString(ds.Tables[0].Rows[i]["UserName"]);
ni.History = new List <NodeHistory>();
if (ds.Tables[0].Rows[i]["TextMsg"] == null || ds.Tables[0].Rows[i]["TextMsg"] == DBNull.Value)
{
nh.TextMsg = string.Empty;
}
else
{
nh.TextMsg = ds.Tables[0].Rows[i]["TextMsg"].ToString();
}
ni.History.Add(nh);
nodes.Add(Util.GetValueOfInt(ds.Tables[0].Rows[i]["AD_WF_Node_ID"]));
nodeInfo.Add(ni);
}
else
{
int index = nodes.IndexOf(Util.GetValueOfInt(ds.Tables[0].Rows[i]["AD_WF_Node_ID"]));
nh = new NodeHistory();
nh.State = Util.GetValueOfString(ds.Tables[0].Rows[i]["WFState"]);
nh.ApprovedBy = Util.GetValueOfString(ds.Tables[0].Rows[i]["UserName"]);
if (ds.Tables[0].Rows[i]["TextMsg"] == null || ds.Tables[0].Rows[i]["TextMsg"] == DBNull.Value)
{
nh.TextMsg = string.Empty;
}
else
{
nh.TextMsg = ds.Tables[0].Rows[i]["TextMsg"].ToString();
}
nodeInfo[index].History.Add(nh);
}
}
info.Node = nodeInfo;
}
return(info);
}
catch
{
return(info);
}
}
} // prepare
/**
* Process
* @return info
* @throws Exception
*/
protected override String DoIt()// throws Exception
{
log.Info("AD_Column_ID=" + p_AD_Column_ID
+ ", IsEncrypted=" + p_IsEncrypted
+ ", ChangeSetting=" + p_ChangeSetting
+ ", MaxLength=" + p_MaxLength);
MColumn column = new MColumn(GetCtx(), p_AD_Column_ID, Get_Trx());
if (column.Get_ID() == 0 || column.Get_ID() != p_AD_Column_ID)
{
throw new Exception("@NotFound@ @AD_Column_ID@ - " + p_AD_Column_ID);
}
//
String columnName = column.GetColumnName();
int dt = column.GetAD_Reference_ID();
// Can it be enabled?
if (column.IsKey() ||
column.IsParent() ||
column.IsStandardColumn() ||
column.IsVirtualColumn() ||
column.IsIdentifier() ||
column.IsTranslated() ||
DisplayType.IsLookup(dt) ||
DisplayType.IsLOB(dt) ||
"DocumentNo".Equals(column.GetColumnName(), StringComparison.OrdinalIgnoreCase) ||
"Value".Equals(column.GetColumnName(), StringComparison.OrdinalIgnoreCase) ||
"Name".Equals(column.GetColumnName(), StringComparison.OrdinalIgnoreCase))
{
if (column.IsEncrypted())
{
column.SetIsEncrypted(false);
column.Save(Get_Trx());
}
return(columnName + ": cannot be encrypted");
}
// Start
AddLog(0, null, null, "Encryption Class = " + SecureEngine.GetClassName());
bool error = false;
// Test Value
if (p_TestValue != null && p_TestValue.Length > 0)
{
String encString = SecureEngine.Encrypt(p_TestValue);
AddLog(0, null, null, "Encrypted Test Value=" + encString);
String clearString = SecureEngine.Decrypt(encString);
if (p_TestValue.Equals(clearString))
{
AddLog(0, null, null, "Decrypted=" + clearString
+ " (same as test value)");
}
else
{
AddLog(0, null, null, "Decrypted=" + clearString
+ " (NOT the same as test value - check algorithm)");
error = true;
}
int encLength = encString.Length;
AddLog(0, null, null, "Test Length=" + p_TestValue.Length + " -> " + encLength);
if (encLength <= column.GetFieldLength())
{
AddLog(0, null, null, "Encrypted Length (" + encLength
+ ") fits into field (" + column.GetFieldLength() + ")");
}
else
{
AddLog(0, null, null, "Encrypted Length (" + encLength
+ ") does NOT fit into field (" + column.GetFieldLength() + ") - resize field");
error = true;
}
}
// Length Test
if (p_MaxLength != 0)
{
String testClear = "1234567890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ";
while (testClear.Length < p_MaxLength)
{
testClear += testClear;
}
testClear = testClear.Substring(0, p_MaxLength);
log.Config("Test=" + testClear + " (" + p_MaxLength + ")");
//
String encString = SecureEngine.Encrypt(testClear);
int encLength = encString.Length;
AddLog(0, null, null, "Test Max Length=" + testClear.Length + " -> " + encLength);
if (encLength <= column.GetFieldLength())
{
AddLog(0, null, null, "Encrypted Max Length (" + encLength
+ ") fits into field (" + column.GetFieldLength() + ")");
}
else
{
AddLog(0, null, null, "Encrypted Max Length (" + encLength
+ ") does NOT fit into field (" + column.GetFieldLength() + ") - resize field");
error = true;
}
}
if (p_IsEncrypted != column.IsEncrypted())
{
if (error || !p_ChangeSetting)
{
AddLog(0, null, null, "Encryption NOT changed - Encryption=" + column.IsEncrypted());
}
else
{
column.SetIsEncrypted(p_IsEncrypted);
if (column.Save(Get_Trx()))
{
AddLog(0, null, null, "Encryption CHANGED - Encryption=" + column.IsEncrypted());
}
else
{
AddLog(0, null, null, "Save Error");
}
}
}
if (p_IsEncrypted == column.IsEncrypted() && !error) // Done By Karan on 10-nov-2016, to encrypt/decrypt passwords according to settings.
{
//object colID = DB.ExecuteScalar("SELECT AD_Column_ID FROM AD_Column WHERE AD_Table_ID =(SELECT AD_Table_ID From AD_Table WHERE TableName='AD_User') AND ColumnName='Password'", null, Get_Trx());
// if (colID != null && colID != DBNull.Value && Convert.ToInt32(colID) == column.GetAD_Column_ID())
//{
string tableName = MTable.GetTableName(GetCtx(), column.GetAD_Table_ID());
DataSet ds = DB.ExecuteDataset("SELECT " + column.GetColumnName() + "," + tableName
+ "_ID FROM " + tableName, null, Get_Trx());
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
if (p_IsEncrypted)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
if (ds.Tables[0].Rows[i][column.GetColumnName()] != null && ds.Tables[0].Rows[i][column.GetColumnName()] != DBNull.Value &&
!SecureEngine.IsEncrypted(ds.Tables[0].Rows[i][column.GetColumnName()].ToString()))
{
//MUser user = new MUser(GetCtx(), Util.GetValueOfInt(ds.Tables[0].Rows[i][MTable.GetTableName(GetCtx(), column.GetAD_Table_ID()) + "_ID"]), Get_Trx());
//user.SetPassword(SecureEngine.Encrypt(ds.Tables[0].Rows[i][column.GetColumnName()].ToString()));
int encLength = SecureEngine.Encrypt(ds.Tables[0].Rows[i][column.GetColumnName()].ToString()).Length;
if (encLength <= column.GetFieldLength())
{
//PO tab = MTable.GetPO(GetCtx(), tableName,
// Util.GetValueOfInt(ds.Tables[0].Rows[i][tableName + "_ID"]), Get_Trx());
//tab.Set_Value(column.GetColumnName(), (SecureEngine.Encrypt(ds.Tables[0].Rows[i][column.GetColumnName()].ToString())));
//if (!tab.Save(Get_Trx()))
//{
// Rollback();
// return "Encryption=" + false;
//}
string p_NewPassword = SecureEngine.Encrypt(ds.Tables[0].Rows[i][column.GetColumnName()].ToString());
String sql = "UPDATE " + tableName + " SET Updated=SYSDATE, UpdatedBy=" + GetAD_User_ID();
if (!string.IsNullOrEmpty(p_NewPassword))
{
sql += ", " + column.GetColumnName() + "=" + GlobalVariable.TO_STRING(p_NewPassword);
}
sql += " WHERE " + tableName + "_ID=" + Util.GetValueOfInt(ds.Tables[0].Rows[i][tableName + "_ID"]);
int iRes = DB.ExecuteQuery(sql, null, Get_Trx());
if (iRes <= 0)
{
Rollback();
return("Encryption=" + false);
}
}
else
{
Rollback();
return("After Encryption some values may exceed the value of column length. Please exceed column Length.");
}
}
}
}
else
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
if (ds.Tables[0].Rows[i][column.GetColumnName()] != null && ds.Tables[0].Rows[i][column.GetColumnName()] != DBNull.Value &&
SecureEngine.IsEncrypted(ds.Tables[0].Rows[i][column.GetColumnName()].ToString()))
{
// MUser user = new MUser(GetCtx(), Util.GetValueOfInt(ds.Tables[0].Rows[i][MTable.GetTableName(GetCtx(), column.GetAD_Table_ID())+"_ID"]), Get_Trx());
//PO tab = MTable.GetPO(GetCtx(), tableName,
// Util.GetValueOfInt(ds.Tables[0].Rows[i][tableName + "_ID"]), Get_Trx());
//tab.Set_Value(column.GetColumnName(), (SecureEngine.Decrypt(ds.Tables[0].Rows[i][column.GetColumnName()].ToString())));
//if (!tab.Save(Get_Trx()))
//{
// Rollback();
// return "Encryption=" + false;
//}
string p_NewPassword = SecureEngine.Decrypt(ds.Tables[0].Rows[i][column.GetColumnName()].ToString());
String sql = "UPDATE " + tableName + " SET Updated=SYSDATE, UpdatedBy=" + GetAD_User_ID();
if (!string.IsNullOrEmpty(p_NewPassword))
{
sql += ", " + column.GetColumnName() + "=" + GlobalVariable.TO_STRING(p_NewPassword);
}
sql += " WHERE " + tableName + "_ID =" + Util.GetValueOfInt(ds.Tables[0].Rows[i][tableName + "_ID"]);
int iRes = DB.ExecuteQuery(sql, null, Get_Trx());
if (iRes <= 0)
{
Rollback();
return("Encryption=" + false);
}
}
}
}
}
//}
}
return("Encryption=" + column.IsEncrypted());
}
} // prepare
/**
* Process
* @return info
* @throws Exception
*/
protected override String DoIt()// throws Exception
{
log.Info("AD_Column_ID=" + p_AD_Column_ID
+ ", IsEncrypted=" + p_IsEncrypted
+ ", ChangeSetting=" + p_ChangeSetting
+ ", MaxLength=" + p_MaxLength);
MColumn column = new MColumn(GetCtx(), p_AD_Column_ID, null);
if (column.Get_ID() == 0 || column.Get_ID() != p_AD_Column_ID)
{
throw new Exception("@NotFound@ @AD_Column_ID@ - " + p_AD_Column_ID);
}
//
String columnName = column.GetColumnName();
int dt = column.GetAD_Reference_ID();
// Can it be enabled?
if (column.IsKey() ||
column.IsParent() ||
column.IsStandardColumn() ||
column.IsVirtualColumn() ||
column.IsIdentifier() ||
column.IsTranslated() ||
DisplayType.IsLookup(dt) ||
DisplayType.IsLOB(dt) ||
"DocumentNo".Equals(column.GetColumnName(), StringComparison.OrdinalIgnoreCase) ||
"Value".Equals(column.GetColumnName(), StringComparison.OrdinalIgnoreCase) ||
"Name".Equals(column.GetColumnName(), StringComparison.OrdinalIgnoreCase))
{
if (column.IsEncrypted())
{
column.SetIsEncrypted(false);
column.Save();
}
return(columnName + ": cannot be encrypted");
}
// Start
AddLog(0, null, null, "Encryption Class = " + SecureEngineUtility.SecureEngine.GetClassName());
bool error = false;
// Test Value
if (p_TestValue != null && p_TestValue.Length > 0)
{
String encString = SecureEngineUtility.SecureEngine.Encrypt(p_TestValue);
AddLog(0, null, null, "Encrypted Test Value=" + encString);
String clearString = SecureEngineUtility.SecureEngine.Decrypt(encString);
if (p_TestValue.Equals(clearString))
{
AddLog(0, null, null, "Decrypted=" + clearString
+ " (same as test value)");
}
else
{
AddLog(0, null, null, "Decrypted=" + clearString
+ " (NOT the same as test value - check algorithm)");
error = true;
}
int encLength = encString.Length;
AddLog(0, null, null, "Test Length=" + p_TestValue.Length + " -> " + encLength);
if (encLength <= column.GetFieldLength())
{
AddLog(0, null, null, "Encrypted Length (" + encLength
+ ") fits into field (" + column.GetFieldLength() + ")");
}
else
{
AddLog(0, null, null, "Encrypted Length (" + encLength
+ ") does NOT fit into field (" + column.GetFieldLength() + ") - resize field");
error = true;
}
}
// Length Test
if (p_MaxLength != 0)
{
String testClear = "1234567890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ";
while (testClear.Length < p_MaxLength)
{
testClear += testClear;
}
testClear = testClear.Substring(0, p_MaxLength);
log.Config("Test=" + testClear + " (" + p_MaxLength + ")");
//
String encString = SecureEngineUtility.SecureEngine.Encrypt(testClear);
int encLength = encString.Length;
AddLog(0, null, null, "Test Max Length=" + testClear.Length + " -> " + encLength);
if (encLength <= column.GetFieldLength())
{
AddLog(0, null, null, "Encrypted Max Length (" + encLength
+ ") fits into field (" + column.GetFieldLength() + ")");
}
else
{
AddLog(0, null, null, "Encrypted Max Length (" + encLength
+ ") does NOT fit into field (" + column.GetFieldLength() + ") - resize field");
error = true;
}
}
if (p_IsEncrypted != column.IsEncrypted())
{
if (error || !p_ChangeSetting)
{
AddLog(0, null, null, "Encryption NOT changed - Encryption=" + column.IsEncrypted());
}
else
{
column.SetIsEncrypted(p_IsEncrypted);
if (column.Save())
{
AddLog(0, null, null, "Encryption CHANGED - Encryption=" + column.IsEncrypted());
}
else
{
AddLog(0, null, null, "Save Error");
}
}
}
return("Encryption=" + column.IsEncrypted());
}
