//
// GET: /VA005/Tree/
public ActionResult GetTreeAsString(bool editable)
{
Ctx ctx = Session["ctx"] as Ctx;
int AD_Table_ID = MTable.Get_Table_ID("M_Product");
var sql = "SELECT AD_Tree_ID FROM AD_Tree "
+ "WHERE AD_Client_ID=" + ctx.GetAD_Client_ID() + " AND AD_Table_ID=" + AD_Table_ID + " AND IsActive='Y' AND IsAllNodes='Y' "
+ "ORDER BY IsDefault DESC, AD_Tree_ID";
int AD_Tree_ID = Util.GetValueOfInt(DB.ExecuteScalar(sql, null, null));
List <SetTree> html = new List <SetTree>();
if (Session["ctx"] != null)
{
var m = new VA005_ProductHelper(Session["ctx"] as Ctx);
var tree = m.GetMenuTree(AD_Tree_ID, editable, true, 0);
List <SetTree> setttreeobj = new List <SetTree>();
SetTree trees = new SetTree();
trees.AD_Tree_ID = AD_Tree_ID;
setttreeobj.Add(trees);
html = m.GetMenuTreeUI(trees, tree.GetRootNode(), tree.GetNodeTableName());
// html = m.GetMenuTreeUI(tree.GetRootNode(), @Url.Content("~/"), windowNo.ToString(), tree.GetNodeTableName());
//m.dispose();
}
return(Json(JsonConvert.SerializeObject(html), JsonRequestBehavior.AllowGet));
}
private bool IsTableAccessForExecute(string sql)
{
sql = sql.Trim();
string[] sqlArray;
if (sql.ToUpper().StartsWith("UPDATE") || sql.ToUpper().StartsWith("DELETE"))
{
sqlArray = sql.Split(' ');
}
else
{
sqlArray = new string[0];
}
if (sqlArray != null && sqlArray.Length > 0)
{
string tableName = sqlArray[1];
if (!string.IsNullOrEmpty(tableName))
{
int tableID = MTable.Get_Table_ID(tableName);
if (tableID <= 0)
{
return(false);
}
if (!MRole.GetDefault(_ctx).IsTableAccess(tableID, false))
{
return(false);
}
}
}
return(true);
}
private bool IsTableAccess(string sql)
{
if (sql.ToUpper().IndexOf("WHERE") > -1)
{
sql = sql.Substring(0, sql.ToUpper().IndexOf("WHERE"));
}
List <string> tables = parseQuery(sql);
for (int i = 0; i < tables.Count; i++)
{
string tableName = tables[i];
if (string.IsNullOrEmpty(tableName) || tableName.Trim().StartsWith("AD_"))
{
continue;
}
int tableID = MTable.Get_Table_ID(tableName);
if (tableID <= 0)
{
return(false);
}
if (!MRole.GetDefault(_ctx).IsTableAccess(tableID, false))
{
return(false);
}
}
return(true);
}
/// <summary>
/// Send notice to user
/// </summary>
/// <param name="AD_User_ID">Id of user</param>
/// <param name="NotificationType"> Notification type</param>
/// <param name="client"> Tenant object</param>
/// <param name="from"> From user notice</param>
/// <param name="subject">Subject of notice.</param>
/// <param name="message">Message to be sent to user</param>
/// <param name="pdf"> Attachment</param>
private void SendNoticeNow(int AD_User_ID, String NotificationType,
MClient client, MUser from, String subject, String message, FileInfo pdf)
{
MUser to = MUser.Get(GetCtx(), AD_User_ID);
if (NotificationType == null)
{
NotificationType = to.GetNotificationType();
}
// Send Mail
if (X_AD_User.NOTIFICATIONTYPE_EMail.Equals(NotificationType) ||
X_AD_User.NOTIFICATIONTYPE_EMailPlusNotice.Equals(NotificationType))
{
VAdvantage.Model.MMailAttachment1 _mAttachment = new VAdvantage.Model.MMailAttachment1(GetCtx(), 0, null);
_mAttachment.SetAD_Client_ID(GetCtx().GetAD_Client_ID());
_mAttachment.SetAD_Org_ID(GetCtx().GetAD_Org_ID());
_mAttachment.SetAD_Table_ID(MTable.Get_Table_ID(Table_Name));
_mAttachment.IsActive();
_mAttachment.SetMailAddress("");
_mAttachment.SetAttachmentType("M");
_mAttachment.SetRecord_ID(_req.GetR_Request_ID());
_mAttachment.SetTextMsg(message);
_mAttachment.SetTitle(subject);
_mAttachment.SetMailAddress(to.GetEMail());
if (from != null && !string.IsNullOrEmpty(from.GetEMail()))
{
_mAttachment.SetMailAddressFrom(from.GetEMail());
}
else
{
_mAttachment.SetMailAddressFrom(client.GetRequestEMail());
}
_mAttachment.NewRecord();
if (client.SendEMail(from, to, subject, message.ToString(), pdf))
{
_success++;
if (_emailTo.Length > 0)
{
_emailTo.Append(", ");
}
_emailTo.Append(to.GetEMail());
_mAttachment.SetIsMailSent(true);
}
else
{
log.Warning("Failed: " + to);
_failure++;
NotificationType = X_AD_User.NOTIFICATIONTYPE_Notice;
_mAttachment.SetIsMailSent(false);
}
_mAttachment.Save();
}
// Send Note
if (X_AD_User.NOTIFICATIONTYPE_Notice.Equals(NotificationType) ||
X_AD_User.NOTIFICATIONTYPE_EMailPlusNotice.Equals(NotificationType))
{
int AD_Message_ID = 834;
MNote note = new MNote(GetCtx(), AD_Message_ID, AD_User_ID,
X_R_Request.Table_ID, _req.GetR_Request_ID(),
subject, message.ToString(), Get_TrxName());
if (note.Save())
{
_notices++;
}
}
}
/// <summary>
/// Send notice to users.
/// </summary>
/// <param name="list"> List of columns changed.</param>
protected void SendNotices(List <String> list)
{
bool isEmailSent = false;
StringBuilder finalMsg = new StringBuilder();
finalMsg.Append(Msg.Translate(GetCtx(), "R_Request_ID") + ": " + _req.GetDocumentNo()).Append("\n").Append(Msg.Translate(GetCtx(), "R_NotificSent"));
// Subject
if (mailText_ID == 0)
{
subject = Msg.Translate(GetCtx(), "R_Request_ID")
+ " " + Msg.GetMsg(GetCtx(), "Updated", true) + ": " + _req.GetDocumentNo() + " (●" + MTable.Get_Table_ID(Table_Name) + "-" + _req.GetR_Request_ID() + "●) " + Msg.GetMsg(GetCtx(), "DoNotChange");
}
// Message
// UpdatedBy: Joe
int UpdatedBy = GetCtx().GetAD_User_ID();
MUser from = MUser.Get(GetCtx(), UpdatedBy);
FileInfo pdf = CreatePDF();
log.Finer(message.ToString());
// Prepare sending Notice/Mail
MClient client = MClient.Get(GetCtx(), GetAD_Client_ID());
// ReSet from if external
if (from.GetEMailUser() == null || from.GetEMailUserPW() == null)
{
from = null;
}
_success = 0;
_failure = 0;
_notices = 0;
/** List of users - aviod duplicates */
List <int> userList = new List <int>();
String sql = "SELECT u.AD_User_ID, u.NotificationType, u.EMail, u.Name, MAX(r.AD_Role_ID) "
+ "FROM RV_RequestUpdates_Only ru"
+ " INNER JOIN AD_User u ON (ru.AD_User_ID=u.AD_User_ID)"
+ " LEFT OUTER JOIN AD_User_Roles r ON (u.AD_User_ID=r.AD_User_ID) "
+ "WHERE ru.R_Request_ID= " + _req.GetR_Request_ID()
+ " GROUP BY u.AD_User_ID, u.NotificationType, u.EMail, u.Name";
IDataReader idr = null;
try
{
idr = DataBase.DB.ExecuteReader(sql, null, null);
while (idr.Read())
{
int AD_User_ID = Utility.Util.GetValueOfInt(idr[0]);
String NotificationType = Util.GetValueOfString(idr[1]); //idr.GetString(1);
if (NotificationType == null)
{
NotificationType = X_AD_User.NOTIFICATIONTYPE_EMail;
}
String email = Util.GetValueOfString(idr[2]);// idr.GetString(2);
if (String.IsNullOrEmpty(email))
{
continue;
}
String Name = Util.GetValueOfString(idr[3]);//idr.GetString(3);
// Role
int AD_Role_ID = Utility.Util.GetValueOfInt(idr[4]);
if (idr == null)
{
AD_Role_ID = -1;
}
// Don't send mail to oneself
// if (AD_User_ID == UpdatedBy)
// continue;
// No confidential to externals
if (AD_Role_ID == -1 &&
(_req.GetConfidentialTypeEntry().Equals(X_R_Request.CONFIDENTIALTYPE_Internal) ||
_req.GetConfidentialTypeEntry().Equals(X_R_Request.CONFIDENTIALTYPE_PrivateInformation)))
{
continue;
}
if (X_AD_User.NOTIFICATIONTYPE_None.Equals(NotificationType))
{
log.Config("Opt out: " + Name);
continue;
}
if ((X_AD_User.NOTIFICATIONTYPE_EMail.Equals(NotificationType) ||
X_AD_User.NOTIFICATIONTYPE_EMailPlusNotice.Equals(NotificationType)) &&
(email == null || email.Length == 0))
{
if (AD_Role_ID >= 0)
{
NotificationType = X_AD_User.NOTIFICATIONTYPE_Notice;
}
else
{
log.Config("No EMail: " + Name);
continue;
}
}
if (X_AD_User.NOTIFICATIONTYPE_Notice.Equals(NotificationType) &&
AD_Role_ID >= 0)
{
log.Config("No internal User: "******"\n").Append(user.GetName()).Append(".");
isEmailSent = true;
}
idr.Close();
// Notification For Role
List <int> _users = SendRoleNotice();
for (int i = 0; i < _users.Count; i++)
{
MUser user = new MUser(GetCtx(), _users[i], null);
int AD_User_ID = user.GetAD_User_ID();
String NotificationType = user.GetNotificationType(); //idr.GetString(1);
if (NotificationType == null)
{
NotificationType = X_AD_User.NOTIFICATIONTYPE_EMail;
}
String email = user.GetEMail();// idr.GetString(2);
if (String.IsNullOrEmpty(email))
{
continue;
}
String Name = user.GetName(); //idr.GetString(3);
// Role
if (X_AD_User.NOTIFICATIONTYPE_None.Equals(NotificationType))
{
log.Config("Opt out: " + Name);
continue;
}
//
SendNoticeNow(_users[i], NotificationType,
client, from, subject, message.ToString(), pdf);
finalMsg.Append("\n").Append(user.GetName()).Append(".");
isEmailSent = true;
}
if (!isEmailSent)
{
finalMsg.Clear();
finalMsg.Append(Msg.Translate(GetCtx(), "R_Request_ID") + ": " + _req.GetDocumentNo()).Append("\n").Append(Msg.Translate(GetCtx(), "R_NoNotificationSent"));
}
int AD_Message_ID = 834;
MNote note = new MNote(GetCtx(), AD_Message_ID, GetCtx().GetAD_User_ID(),
X_R_Request.Table_ID, _req.GetR_Request_ID(),
subject, finalMsg.ToString(), Get_TrxName());
if (note.Save())
{
log.Log(Level.INFO, "ProcessFinished", "");
}
}
catch (Exception e)
{
if (idr != null)
{
idr.Close();
}
log.Log(Level.SEVERE, sql, e);
}
// New Sales Rep (may happen if sent from beforeSave
if (!userList.Contains(_req.GetSalesRep_ID()))
{
SendNoticeNow(_req.GetSalesRep_ID(), null,
client, from, subject, message.ToString(), pdf);
}
}
/// <summary>
/// Get All Users of current client and org
/// </summary>
/// <param name="searchText"></param>
/// <param name="sortBy"></param>
/// <param name="pageNo"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public List <UserInfo> GetUserInfo(string searchText, int sortBy, int pageNo, int pageSize)
{
List <UserInfo> uInfo = new List <UserInfo>();
int UserTableID = MTable.Get_Table_ID("AD_User");
int UserWindowID = Convert.ToInt32(DB.ExecuteScalar("SELECT AD_Window_ID from AD_Window WHERE Name='User'", null, null));
if (!(bool)MRole.GetDefault(ctx).GetWindowAccess(UserWindowID))
{
return(uInfo);
}
if (!MRole.GetDefault(ctx).IsTableAccess(UserTableID, false))
{
return(uInfo);
}
string sql = @"SELECT AD_User.Name,
AD_User.Email,
AD_User.AD_User_ID,
AD_User.IsActive,
AD_User.AD_Image_ID,
AD_User.AD_Client_ID,
AD_User.AD_Org_ID,
C_Country.Name as CName
FROM AD_User
LEFT OUTER JOIN C_LOcation
ON AD_User.C_Location_ID=C_Location.C_Location_ID
LEFT OUTER JOIN C_Country
ON C_Country.C_Country_ID=C_Location.C_Country_ID WHERE IsLoginUser='******' ";
if (!String.IsNullOrEmpty(searchText))
{
sql += " AND ( upper(AD_User.Value) like Upper('%" + searchText + "%') OR upper(AD_User.Name) like Upper('%" + searchText + "%') OR upper(AD_User.Email) like Upper('%" + searchText + "%'))";
}
sql += " ORDER BY AD_User.IsActive desc";
if (sortBy == -1 || sortBy == 1)
{
sql += " , upper(AD_User.Name) ASC";
}
else if (sortBy == 2)
{
sql += " , upper(AD_User.Value) ASC";
}
else if (sortBy == 3)
{
sql += " , upper(AD_User.Email) ASC";
}
sql = MRole.GetDefault(ctx).AddAccessSQL(sql, "AD_User", true, false);
DataSet ds = DB.ExecuteDatasetPaging(sql, pageNo, pageSize);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
UserInfo userInfo = new UserInfo();
userInfo.HasAccess = MRole.GetDefault(ctx).IsRecordAccess(UserTableID, Convert.ToInt32(ds.Tables[0].Rows[i]["AD_User_ID"]), true);
userInfo.Username = Convert.ToString(ds.Tables[0].Rows[i]["Name"]);
userInfo.Email = Convert.ToString(ds.Tables[0].Rows[i]["Email"]);
userInfo.AD_UserID = Convert.ToInt32(ds.Tables[0].Rows[i]["AD_User_ID"]);
userInfo.AD_OrgID = Convert.ToInt32(ds.Tables[0].Rows[i]["AD_Org_ID"]);
userInfo.AD_ClientID = Convert.ToInt32(ds.Tables[0].Rows[i]["AD_Client_ID"]);
userInfo.Country = Convert.ToString(ds.Tables[0].Rows[i]["CName"]);
userInfo.UserTableID = UserTableID;
userInfo.UserWindowID = UserWindowID;
userInfo.IsActive = ds.Tables[0].Rows[i]["IsActive"].ToString() == "Y" ? true : false;
userInfo.IsUpdate = MRole.GetDefault(ctx).CanUpdate(userInfo.AD_ClientID, userInfo.AD_OrgID, userInfo.UserTableID, userInfo.AD_UserID, false);
if (ds.Tables[0].Rows[i]["AD_Image_ID"] != DBNull.Value && ds.Tables[0].Rows[i]["AD_Image_ID"] != null && Convert.ToInt32(ds.Tables[0].Rows[i]["AD_Image_ID"]) > 0)
{
MImage mimg = new MImage(ctx, Convert.ToInt32(ds.Tables[0].Rows[i]["AD_Image_ID"]), null);
var imgfll = mimg.GetThumbnailURL(46, 46);
userInfo.UserImage = imgfll;
if (userInfo.UserImage == "FileDoesn'tExist" || userInfo.UserImage == "NoRecordFound")
{
userInfo.UserImage = "";
}
}
else
{
userInfo.UserImage = "";
}
uInfo.Add(userInfo);
}
}
return(uInfo);
}
/// <summary>
/// Get all the roles of current User. if a role is assigned then it will be shown as checked otherwise unchecked
/// </summary>
/// <param name="AD_User_ID"></param>
/// <param name="name"></param>
/// <returns></returns>
public List <RolesInfo> GetRoleInfo(int AD_User_ID, string name)
{
List <RolesInfo> rInfo = new List <RolesInfo>();
int RoleWindowID = Convert.ToInt32(DB.ExecuteScalar("SELECT AD_Window_ID from AD_Window WHERE Name='Role'", null, null));
if (!(bool)MRole.GetDefault(ctx).GetWindowAccess(RoleWindowID))
{
return(rInfo);
}
int UserTableID = MTable.Get_Table_ID("AD_User");
bool IsUpdate = true;
String sql = "SELECT AD_Client_ID, AD_ORg_ID from AD_User WHERE AD_User_ID=" + AD_User_ID;
DataSet ds = DB.ExecuteDataset(sql);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
IsUpdate = MRole.GetDefault(ctx).CanUpdate(Convert.ToInt32(ds.Tables[0].Rows[0]["AD_Client_ID"]), Convert.ToInt32(ds.Tables[0].Rows[0]["AD_Org_ID"]), UserTableID, AD_User_ID, false);
}
sql = @"Select AD_Role.AD_Role_ID, AD_Role.Name from AD_Role WHERE AD_Role.AD_Client_ID=" + ctx.GetAD_Client_ID() + " AND AD_Role.AD_Role_ID > 0 AND IsActive='Y'";
// string sql = @"SELECT AD_Role.AD_Role_ID,
// AD_Role.Name
// FROM AD_Role
// JOIN ad_user_roles
//
// on AD_Role.AD_role_ID=ad_user_roles.AD_Role_ID
// WHERE AD_User_Roles.IsActive='Y' AND AD_Role.IsActive='Y' AND AD_User_Roles.AD_User_ID=" + ctx.GetAD_User_ID();
if (name != null && name.Length > 0)
{
sql += " AND upper(AD_Role.Name) like ('%" + name.ToUpper() + "%')";
}
sql += " ORDER BY upper(AD_Role.Name)";
sql = MRole.GetDefault(ctx).AddAccessSQL(sql, "AD_Role", true, false);
ds = DB.ExecuteDataset(sql);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
RolesInfo roleInfo = new RolesInfo();
roleInfo.AD_Role_ID = Convert.ToInt32(ds.Tables[0].Rows[i]["AD_Role_ID"]);
roleInfo.Name = Convert.ToString(ds.Tables[0].Rows[i]["Name"]);
roleInfo.IsAssignedToUser = false;
roleInfo.roleWindowID = RoleWindowID;
roleInfo.IsUpdate = IsUpdate;
rInfo.Add(roleInfo);
}
sql = "Select AD_Role_ID, IsActive from ad_user_roles where AD_User_ID=" + AD_User_ID;
DataSet dsURoles = DB.ExecuteDataset(sql);
if (dsURoles != null && dsURoles.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < dsURoles.Tables[0].Rows.Count; i++)
{
RolesInfo rrinfo = rInfo.Where(a => a.AD_Role_ID == Convert.ToInt32(dsURoles.Tables[0].Rows[i]["AD_Role_ID"])).FirstOrDefault();
if (rrinfo != null)
{
if (dsURoles.Tables[0].Rows[i]["IsActive"].ToString().Equals("Y"))
{
rrinfo.IsAssignedToUser = true;
}
else
{
rrinfo.IsAssignedToUser = false;
}
}
}
}
}
rInfo = rInfo.OrderBy(a => !a.IsAssignedToUser).ToList();
return(rInfo);
}
} // reset
/// <summary>
/// Delete Accounting Table where period status is open and update count.
/// </summary>
/// <param name="TableName">table name</param>
/// <param name="AD_Table_ID">table</param>
private void Delete(String TableName, int AD_Table_ID)
{
Reset(TableName);
_countReset = 0;
bypass = false;
//
/// Change by Mohit, Check applied for GL Journal,Profit & Loss, Income Tax Reset accounting, and logic changes to pick table ID from
/// MTable rather than static constructor of mclass. Askes By Mukesh Sir, Amit Date=04/08/2017
String docBaseType = null;
if (AD_Table_ID == MTable.Get_Table_ID("C_Invoice"))
{
docBaseType = "IN ('" + MDocBaseType.DOCBASETYPE_APINVOICE
+ "','" + MDocBaseType.DOCBASETYPE_APCREDITMEMO
+ "','" + MDocBaseType.DOCBASETYPE_ARINVOICE
+ "','" + MDocBaseType.DOCBASETYPE_ARCREDITMEMO
+ "','" + MDocBaseType.DOCBASETYPE_ARPROFORMAINVOICE + "')";
}
else if (AD_Table_ID == MTable.Get_Table_ID("M_InOut"))
{
docBaseType = "IN ('" + MDocBaseType.DOCBASETYPE_MATERIALDELIVERY
+ "','" + MDocBaseType.DOCBASETYPE_MATERIALRECEIPT + "')";
}
else if (AD_Table_ID == MTable.Get_Table_ID("C_Payment"))
{
docBaseType = "IN ('" + MDocBaseType.DOCBASETYPE_APPAYMENT
+ "','" + MDocBaseType.DOCBASETYPE_ARRECEIPT + "')";
}
else if (AD_Table_ID == MTable.Get_Table_ID("C_Order"))
{
docBaseType = "IN ('" + MDocBaseType.DOCBASETYPE_SALESORDER
+ "','" + MDocBaseType.DOCBASETYPE_PURCHASEORDER + "')";
}
else if (AD_Table_ID == MTable.Get_Table_ID("C_ProjectIssue"))
{
docBaseType = "= '" + MDocBaseType.DOCBASETYPE_PROJECTISSUE + "'";
}
else if (AD_Table_ID == MTable.Get_Table_ID("C_BankStatement"))
{
docBaseType = "= '" + MDocBaseType.DOCBASETYPE_BANKSTATEMENT + "'";
}
else if (AD_Table_ID == MTable.Get_Table_ID("C_Cash"))
{
docBaseType = "= '" + MDocBaseType.DOCBASETYPE_CASHJOURNAL + "'";
}
else if (AD_Table_ID == MTable.Get_Table_ID("C_AllocationHdr"))
{
docBaseType = "= '" + MDocBaseType.DOCBASETYPE_PAYMENTALLOCATION + "'";
}
else if (AD_Table_ID == MTable.Get_Table_ID("GL_Journal"))
{
docBaseType = "= '" + MDocBaseType.DOCBASETYPE_GLJOURNAL + "'";
}
// else if (AD_Table_ID == M.Table_ID)
// {
// docBaseType = "= '" + MDocBaseType.DOCBASETYPE_GLDocument + "'";
// }
else if (AD_Table_ID == MTable.Get_Table_ID("M_Movement"))
{
docBaseType = "= '" + MDocBaseType.DOCBASETYPE_MATERIALMOVEMENT + "'";
}
else if (AD_Table_ID == MTable.Get_Table_ID("M_Requisition"))
{
docBaseType = "= '" + MDocBaseType.DOCBASETYPE_PURCHASEREQUISITION + "'";
}
else if (AD_Table_ID == MTable.Get_Table_ID("M_Inventory"))
{
docBaseType = "= '" + MDocBaseType.DOCBASETYPE_MATERIALPHYSICALINVENTORY + "'";
}
else if (AD_Table_ID == MTable.Get_Table_ID("M_Production"))
{
docBaseType = "= '" + MDocBaseType.DOCBASETYPE_MATERIALPRODUCTION + "'";
}
else if (AD_Table_ID == MTable.Get_Table_ID("M_MatchInv"))
{
docBaseType = "= '" + MDocBaseType.DOCBASETYPE_MATCHINVOICE + "'";
}
else if (AD_Table_ID == MTable.Get_Table_ID("M_MatchPO"))
{
docBaseType = "= '" + MDocBaseType.DOCBASETYPE_MATCHPO + "'";
}
else if (AD_Table_ID == MTable.Get_Table_ID("C_IncomeTax"))
{
docBaseType = "= '" + MDocBaseType.DOCBASETYPE_INCOMETAX + "'";
}
else if (AD_Table_ID == MTable.Get_Table_ID("C_ProfitLoss"))
{
docBaseType = "= '" + MDocBaseType.DOCBASETYPE_PROFITLOSS + "'";
}
else if (AD_Table_ID == MTable.Get_Table_ID("VAFAM_AssetDepreciation"))
{
docBaseType = "= '" + MDocBaseType.DOCBASETYPE_FIXASSET + "'";
}
else if (AD_Table_ID == MTable.Get_Table_ID("VAFAM_AssetImpairEnhance"))
{
docBaseType = "= '" + MDocBaseType.DOCBASETYPE_FIXASSETIMPAIRMENT + "'";
}
else if (AD_Table_ID == MTable.Get_Table_ID("VA026_LCDetail"))
{
docBaseType = "= '" + MDocBaseType.DOCBASETYPE_LETTEROFCREDIT + "'";
}
else if (AD_Table_ID == MTable.Get_Table_ID("VA024_ObsoleteInventory"))
{
docBaseType = "= '" + MDocBaseType.DOCBASETYPE_INVENTORYPOVISION + "'";
}
else if (AD_Table_ID == MTable.Get_Table_ID("VA027_PostDatedCheck"))
{
docBaseType = " IN ( '" + MDocBaseType.DOCBASETYPE_PDCPAYABLE + "' ,'" + MDocBaseType.DOCBASETYPE_PDCRECEIVABLE + "' )";
}
else if (AD_Table_ID == MTable.Get_Table_ID("VAMFG_M_WorkOrder") || AD_Table_ID == MTable.Get_Table_ID("VAMFG_M_WrkOdrTransaction"))
{
bypass = true;
docBaseType = null;
}
//
if (docBaseType == null && !bypass)
{
String s = TableName + ": Unknown DocBaseType";
log.Severe(s);
AddLog(s);
docBaseType = "";
return;
}
else if (!string.IsNullOrEmpty(docBaseType))
{
docBaseType = " AND pc.DocBaseType " + docBaseType;
}
// Doc
String sql1 = "UPDATE " + TableName + " doc"
+ " SET Posted='N', Processing='N' "
+ "WHERE AD_Client_ID=" + _AD_Client_ID
+ " AND (Posted<>'N' OR Posted IS NULL OR Processing<>'N' OR Processing IS NULL)"
+ " AND EXISTS (SELECT * FROM C_PeriodControl pc"
+ " INNER JOIN Fact_Acct fact ON (fact.C_Period_ID=pc.C_Period_ID) "
+ "WHERE pc.PeriodStatus = 'O'" + docBaseType
+ " AND fact.AD_Table_ID=" + AD_Table_ID
+ " AND fact.Record_ID=doc." + TableName + "_ID)";
int reset = DataBase.DB.ExecuteQuery(sql1, null, Get_TrxName());
// Fact
String sql2 = "DELETE FROM Fact_Acct fact "
+ "WHERE AD_Client_ID=" + _AD_Client_ID
+ " AND AD_Table_ID=" + AD_Table_ID
+ " AND EXISTS (SELECT * FROM C_PeriodControl pc "
+ "WHERE pc.PeriodStatus = 'O'" + docBaseType
+ " AND fact.C_Period_ID=pc.C_Period_ID)";
int deleted = DataBase.DB.ExecuteQuery(sql2, null, Get_TrxName());
//
log.Info(TableName + "(" + AD_Table_ID + ") - Reset=" + reset + " - Deleted=" + deleted);
String s1 = TableName + " - Reset=" + reset + " - Deleted=" + deleted;
AddLog(s1);
if (reset == 0)
{
log.Finest(sql1);
}
if (deleted == 0)
{
log.Finest(sql2);
}
//
_countReset += reset;
_countDelete += deleted;
} // delete
/// <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("");
}
