private String ManageExportID(int PrimaryKey, String tableName, String refTable)
{
try
{
if (String.IsNullOrEmpty(refTable))
{
refTable = tableName;
}
if (module == null)
{
module = new X_AD_ModuleInfo(GetCtx(), _AD_ModuleInfo_ID, null);
}
//String exportid = module.GetPrefix() + PrimaryKey; //export id to be picked from msequence table
String exportid = module.GetPrefix() + MSequence.GetNextExportID(GetCtx().GetAD_Client_ID(), tableName, null);
String _updateSql = "Update " + refTable + " SET Export_ID = " + DB.TO_STRING(exportid) + " WHERE " + tableName + "_ID = " + PrimaryKey;
int result = DB.ExecuteQuery(_updateSql);
return(exportid);
}
catch
{
return("");
}
}
internal static void SaveLoginSetting(LoginModel model)
{
try
{
int id = MSequence.GetNextID(Convert.ToInt32(model.Login2Model.Client), "AD_LoginSetting", (Trx)null);
if (id > 0)
{
string sql = "INSERT INTO AD_LoginSetting " +
"(AD_Client_ID,AD_LoginSetting_ID,AD_Org_ID,AD_Role_ID,AD_User_ID,Created,CreatedBy,IsActive,M_WareHouse_ID,Updated,UpdatedBy) " +
" VALUES (" + model.Login2Model.Client + "," + id + "," + model.Login2Model.Org + "," + model.Login2Model.Role + "," + model.Login1Model.AD_User_ID + ",sysdate," + model.Login1Model.AD_User_ID + ",'Y',";
if (!String.IsNullOrEmpty(model.Login2Model.Warehouse) && model.Login2Model.Warehouse != "-1")
{
sql += model.Login2Model.Warehouse + ",";
}
else
{
sql += "null,";
}
sql += "sysdate," + model.Login1Model.AD_User_ID + ")";
DB.ExecuteQuery(sql);
}
}
catch
{
}
}
/// <summary>
/// Set the document number from Completed Document Sequence after completed
/// </summary>
private void SetCompletedDocumentNo()
{
MDocType dt = MDocType.Get(GetCtx(), GetC_DocType_ID());
// if Overwrite Date on Complete checkbox is true.
if (dt.IsOverwriteDateOnComplete())
{
SetDateDoc(DateTime.Now.Date);
// Std Period open?
if (!MPeriod.IsOpen(GetCtx(), GetDateDoc(), dt.GetDocBaseType()))
{
throw new Exception("@PeriodClosed@");
}
}
// if Overwrite Sequence on Complete checkbox is true.
if (dt.IsOverwriteSeqOnComplete())
{
// Set Drafted Document No into Temp Document No.
if (Get_ColumnIndex("TempDocumentNo") > 0)
{
SetTempDocumentNo(GetDocumentNo());
}
// Get current next from Completed document sequence defined on Document type
String value = MSequence.GetDocumentNo(GetC_DocType_ID(), Get_TrxName(), GetCtx(), true, this);
if (value != null)
{
SetDocumentNo(value);
}
}
}
private String ManageExportID(int PrimaryKey, String tableName, String refTable)
{
try
{
if (String.IsNullOrEmpty(refTable))
{
refTable = tableName;
}
if (module == null)
{
module = new X_AD_ModuleInfo(GetCtx(), _AD_ModuleInfo_ID, null);
}
//String exportid = module.GetPrefix() + PrimaryKey; //export id to be picked from msequence table
int expID = MSequence.GetNextExportID(GetCtx().GetAD_Client_ID(), tableName, null);
if (expID == -1)
{
throw new InvalidConstraintException("ExportID -1 for TableName: " + tableName);
}
String exportid = module.GetPrefix() + expID;
String _updateSql = "Update " + refTable + " SET Export_ID = " + DB.TO_STRING(exportid) + " WHERE " + tableName + "_ID = " + PrimaryKey;
int result = DB.ExecuteQuery(_updateSql);
return(exportid);
}
catch (Exception ex)
{
throw ex;
return("");
}
}
} // afterSave
/// <summary>
/// After Delete
/// </summary>
/// <param name="success">success</param>
/// <returns>true if success</returns>
protected override Boolean AfterDelete(Boolean success)
{
if (success)
{
MSequence.DeleteTableSequence(GetCtx(), GetTableName(), Get_TrxName());
}
return(success);
} // afterDelete
/// <summary>
/// Get next number for Key column = 0 is Error.
/// @ejb.interface-method view-type="both"
/// </summary>
/// <param name="AD_Client_ID">client</param>
/// <param name="TableName">table name</param>
/// <param name="trxName">optional Transaction Name</param>
/// <returns>next no</returns>
public int GetNextID(int AD_Client_ID, String TableName, Trx trxName)
{
int retValue = MSequence.GetNextID(AD_Client_ID, TableName, trxName);
//log.Finer("[" + _no + "] " + TableName + " = " + retValue);
_nextSeqCount++;
return(retValue);
}
/// <summary>
/// Create Sequence for given table.
/// </summary>
/// <param name="table"></param>
/// <param name="trxName"></param>
private void CreateSequence(MTable table, Trx trxName)
{
if (!table.IsView())
{
if (!MSequence.CreateTableSequence(GetCtx(), table.GetTableName(), trxName))
{
//throw new Exception("Can not create Native Sequence for table " + table.GetTableName());
this.AddLog("Can not create Native Sequence for table : " + table.GetTableName());
}
else
{
this.AddLog("Create Native Sequence for : " + table.GetTableName());
}
}
}
private String ManageExportID(int recordID, int AD_Colone_ID, String tableName, int _table_ID)
{
try
{
//if (String.IsNullOrEmpty(refTable))
// refTable = tableName;
if (module == null)
{
module = new X_AD_ModuleInfo(GetCtx(), _AD_ModuleInfo_ID, null);
}
//String exportid = module.GetPrefix() + PrimaryKey; //export id to be picked from msequence table
int expID = MSequence.GetNextExportID(GetCtx().GetAD_Client_ID(), tableName, null);
if (expID == -1)
{
throw new InvalidConstraintException("ExportID -1 for TableName: " + tableName);
}
String exportid = module.GetPrefix() + expID;
string[] ds = GetParentColumns(_table_ID);
//String _updateSql = "Update " + tableName + " SET Export_ID = " + DB.TO_STRING(exportid) + " WHERE " + tableName + "_ID = " + recordID;
String _updateSql = "Update " + tableName + " SET Export_ID = " + DB.TO_STRING(exportid) + " WHERE ";
// _updateSql += ds.Tables[0].Rows[0]["ColumnName"].ToString() + " =" + recordID;
_updateSql += ds[0] + " =" + recordID;
if (ds.Length > 1)
{
//_updateSql += " and " + ds.Tables[0].Rows[1]["ColumnName"].ToString() + " =" + AD_Colone_ID;
_updateSql += " and " + ds[1] + " =" + AD_Colone_ID;
}
int result = DB.ExecuteQuery(_updateSql);
return(exportid);
}
catch (Exception ex)
{
throw ex;
return("");
}
}
} // checkTableID
/// <summary>
/// Check/Initialize DocumentNo/Value Sequences for all Clients
/// </summary>
/// <param name="ctx">context</param>
/// <param name="sp">server process or null</param>
private static void CheckClientSequences(Ctx ctx, SvrProcess sp)
{
Trx trxName = null;
if (sp != null)
{
trxName = sp.Get_Trx();
}
// Sequence for DocumentNo/Value
MClient[] clients = MClient.GetAll(ctx);
for (int i = 0; i < clients.Length; i++)
{
MClient client = clients[i];
if (!client.IsActive())
{
continue;
}
MSequence.CheckClientSequences(ctx, client.GetAD_Client_ID(), trxName);
} // for all clients
} // checkClientSequences
/// <summary>
/// After Save
/// </summary>
/// <param name="newRecord">newRecord new</param>
/// <param name="success">success</param>
/// <returns>success</returns>
protected override bool AfterSave(bool newRecord, bool success)
{
if (!success)
{
return(false);
}
// Sync Table ID
if (newRecord)
{
MSequence.CreateTableSequence(GetCtx(), GetTableName(), Get_TrxName());
}
else
{
MSequence seq = MSequence.Get(GetCtx(), GetTableName(), Get_TrxName());
if (seq == null || seq.Get_ID() == 0)
{
MSequence.CreateTableSequence(GetCtx(), GetTableName(), Get_TrxName());
}
else if (!seq.GetName().Equals(GetTableName()))
{
seq.SetName(GetTableName());
seq.Save();
}
}
// checked if value is changed in Maintain Version
if (Is_ValueChanged("IsMaintainVersions"))
{
if (!newRecord && IsMaintainVersions())
{
int ColID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT AD_Column_ID FROM AD_Column WHERE IsActive = 'Y' AND AD_Table_ID = " + GetAD_Table_ID() + " ORDER BY ColumnName", null, Get_Trx()));
if (ColID > 0)
{
MColumn column = new MColumn(GetCtx(), ColID, Get_Trx());
MasterVersions mv = new MasterVersions();
string versionMsg = mv.CreateVersionInfo(column.GetAD_Column_ID(), column.GetAD_Table_ID(), Get_Trx());
}
}
}
return(success);
} // afterSave
/// <summary>
/// After Save
/// </summary>
/// <param name="newRecord">newRecord new</param>
/// <param name="success">success</param>
/// <returns>success</returns>
protected override bool AfterSave(bool newRecord, bool success)
{
// Sync Table ID
if (newRecord)
{
MSequence.CreateTableSequence(GetCtx(), GetTableName(), Get_TrxName());
}
else
{
MSequence seq = MSequence.Get(GetCtx(), GetTableName(), Get_TrxName());
if (seq == null || seq.Get_ID() == 0)
{
MSequence.CreateTableSequence(GetCtx(), GetTableName(), Get_TrxName());
}
else if (!seq.GetName().Equals(GetTableName()))
{
seq.SetName(GetTableName());
seq.Save();
}
}
return(success);
} // afterSave
public string SelectionSave(VA005_SaveSelectionList value)
{
MAttributeValue obj = new MAttributeValue(_ctx, 0, null);
obj.SetName(value.secname);
if (String.IsNullOrEmpty(value.searchkey))
{
//obj.SetValue(value.secname);
obj.SetValue(MSequence.GetDocumentNo(_ctx.GetAD_Client_ID(), "AD_Org", null, _ctx));
}
else
{
obj.SetValue(value.searchkey);
}
obj.SetM_Attribute_ID(value.attributeID);
if (obj.Save())
{
return(obj.GetM_AttributeValue_ID().ToString());
}
return(Msg.GetMsg(_ctx, "VA005_UnableToSelectionSave"));
}
//save user settings
public UserSetting SaveUserSettings(Ctx ctx, int AD_User_ID, string currentPws, string newPws, bool chkEmail, bool chkNotice,
bool chkSMS, bool chkFax, string emailUserName, string emailPws, int AD_Role_ID, int AD_Client_ID, int AD_Org_ID, int M_Warehouse_ID)
{
UserSetting obj = new UserSetting();
obj.Msg = Msg.GetMsg(ctx, "RecordSaved");
MUser user = new MUser(ctx, AD_User_ID, null);
obj.IsSaved = false;
//if (currentPws.Length > 0 && newPws.Length > 0)
//{
// if (user.GetPassword().ToUpper() != currentPws.ToUpper())
// {
// obj.Msg = Msg.GetMsg(ctx, "InCorrectPassword!");
// obj.IsSaved = false;
// return obj;
// }
// user.SetPassword(newPws);
//}
//user.SetIsEmail(chkEmail);
//user.SetNotificationType(X_AD_User.NOTIFICATIONTYPE_EMail);
////notice
//user.SetIsSms(chkSMS);
//user.SetNotificationType(X_AD_User.NOTIFICATIONTYPE_SMS);
//if (chkNotice && chkFax)
//{
// user.SetNotificationType(X_AD_User.NOTIFICATIONTYPE_NoticePlusFaxEMail);
//}
//else if (chkEmail && chkNotice)
//{
// user.SetNotificationType(X_AD_User.NOTIFICATIONTYPE_EMailPlusNotice);
//}
//else if (chkEmail && chkFax)
//{
// user.SetNotificationType(X_AD_User.NOTIFICATIONTYPE_EMailPlusFaxEMail);
//}
//else if (chkEmail && chkSMS && !chkNotice && !chkFax)
//{
// user.SetNotificationType(X_AD_User.NOTIFICATIONTYPE_None);
//}
//else if (chkNotice && chkSMS)
//{
// user.SetNotificationType(X_AD_User.NOTIFICATIONTYPE_Notice);
//}
//else if (chkFax && chkSMS)
//{
// user.SetNotificationType(X_AD_User.NOTIFICATIONTYPE_FaxEMail);
//}
user.SetIsEmail(chkEmail);
user.SetIsSms(chkSMS);
if (chkEmail)
{
user.SetNotificationType(X_AD_User.NOTIFICATIONTYPE_EMail);
if (chkNotice)
{
user.SetNotificationType(X_AD_User.NOTIFICATIONTYPE_EMailPlusNotice);
}
if (chkFax)
{
user.SetNotificationType(X_AD_User.NOTIFICATIONTYPE_EMailPlusFaxEMail);
}
}
if (chkSMS)
{
user.SetNotificationType(X_AD_User.NOTIFICATIONTYPE_SMS);
if (chkNotice)
{
user.SetNotificationType(X_AD_User.NOTIFICATIONTYPE_Notice);
}
if (chkFax)
{
user.SetNotificationType(X_AD_User.NOTIFICATIONTYPE_FaxEMail);
}
}
if (chkFax)
{
user.SetNotificationType(X_AD_User.NOTIFICATIONTYPE_FaxEMail);
}
if (chkNotice)
{
user.SetNotificationType(X_AD_User.NOTIFICATIONTYPE_Notice);
if (chkFax)
{
user.SetNotificationType(X_AD_User.NOTIFICATIONTYPE_NoticePlusFaxEMail);
}
}
if (!chkNotice && !chkFax)
{
user.SetNotificationType(X_AD_User.NOTIFICATIONTYPE_None);
}
//fax
user.SetEMailUser(emailUserName);
user.SetEMailUserPW(emailPws);
if (user.Save())
{
obj.IsSaved = true;
}
try
{
//LoginSettings
int AD_LoginSetting_ID = Util.GetValueOfInt(VAdvantage.DataBase.DB.ExecuteScalar("SELECT AD_LoginSetting_ID FROM AD_LoginSetting WHERE IsActive='Y' AND AD_User_ID=" + AD_User_ID, null, null));
StringBuilder sql = new StringBuilder("");
if (AD_LoginSetting_ID > 0)//UPdate
{
sql.Append("UPDATE AD_LoginSetting SET AD_Role_ID=" + AD_Role_ID + ",AD_Client_ID=" + AD_Client_ID + ",AD_Org_ID=" + AD_Org_ID + " ,M_Warehouse_ID=");
if (M_Warehouse_ID == 0)
{
sql.Append("NULL");
}
else
{
sql.Append(M_Warehouse_ID);
}
sql.Append(" WHERE AD_LoginSetting_ID=" + AD_LoginSetting_ID);
}
else//Insert
{
AD_LoginSetting_ID = MSequence.GetNextID(ctx.GetAD_Client_ID(), "AD_LoginSetting", null);
sql.Append("INSERT INTO AD_LoginSetting (AD_CLIENT_ID,AD_LOGINSETTING_ID,AD_ORG_ID,AD_ROLE_ID,AD_USER_ID,CREATED,CREATEDBY,EXPORT_ID,M_WAREHOUSE_ID,UPDATED,UPDATEDBY)");
sql.Append(" VALUES (" + AD_Client_ID + "," + AD_LoginSetting_ID + "," + AD_Org_ID + "," + AD_Role_ID + "," + AD_User_ID + ",");
sql.Append(GlobalVariable.TO_DATE(DateTime.Now, false) + "," + ctx.GetAD_User_ID() + ",NULL,");
if (M_Warehouse_ID == 0)
{
sql.Append("NULL");
}
else
{
sql.Append(M_Warehouse_ID);
}
sql.Append("," + GlobalVariable.TO_DATE(DateTime.Now, false) + "," + ctx.GetAD_User_ID() + ")");
}
int s = VAdvantage.DataBase.DB.ExecuteQuery(sql.ToString(), null, null);
if (s == -1)
{
obj.IsSaved = false;
}
}
catch
{
}
return(obj);
}
protected override string DoIt()
{
MProfitLoss PL = new MProfitLoss(GetCtx(), GetRecord_ID(), null);
prof = new MProfitLoss(GetCtx(), GetRecord_ID(), Get_Trx());
stDate = Util.GetValueOfDateTime(DB.ExecuteScalar("select p.startdate from c_period p inner join c_year y on p.c_year_id=y.c_year_id where p.periodno='1' and p.c_year_id= " + prof.GetC_Year_ID() + " and y.ad_client_id= " + GetAD_Client_ID(), null, null));
eDate = Util.GetValueOfDateTime(DB.ExecuteScalar("select p.enddate from c_period p inner join c_year y on p.c_year_id=y.c_year_id where p.periodno='12' and p.c_year_id= " + prof.GetC_Year_ID() + " and y.ad_client_id= " + GetAD_Client_ID(), null, null));
// lock record
lock (lockRecord)
{
sql.Clear();
sql.Append("SELECT distinct CP.* FROM C_ProfitLoss CP INNER JOIN Fact_Acct ft ON ft.C_AcctSchema_ID = Cp.C_AcctSchema_ID "
+ " INNER JOIN c_elementvalue ev ON ft.account_id =ev.c_elementvalue_id "
+ " WHERE CP.ad_client_id = " + GetAD_Client_ID());
if (prof.Get_Value("PostingType") != null)
{
sql.Append(" and CP.PostingType = '" + prof.Get_Value("PostingType") + "' ");
}
sql.Append(" AND (( " + GlobalVariable.TO_DATE(prof.GetDateFrom(), true) + " > = CP.DateFrom "
+ " AND " + GlobalVariable.TO_DATE(prof.GetDateFrom(), true) + " < = CP.DateTo "
+ " OR " + GlobalVariable.TO_DATE(prof.GetDateTo(), true) + " < = CP.DateFrom "
+ " AND " + GlobalVariable.TO_DATE(prof.GetDateTo(), true) + " < = CP.DateTo )) "
+ " AND (ev.accounttype ='E' OR ev.accounttype ='R') "
+ " AND ev.isintermediatecode='N' AND CP.AD_Org_ID IN ( (SELECT Ad_Org_ID FROM AD_Org WHERE isactive = 'Y' "
+ " AND (legalentityorg =" + PL.GetAD_Org_ID() + " OR Ad_Org_ID = " + PL.GetAD_Org_ID() + ") )) AND DOCstatus in ('CO', 'CL') ");
if (Util.GetValueOfInt(PL.Get_Value("C_AcctSchema_ID")) > 0)
{
sql.Append(" AND Cp.C_AcctSchema_ID=" + Util.GetValueOfInt(PL.Get_Value("C_AcctSchema_ID")));
}
ds1 = DB.ExecuteDataset(sql.ToString(), null, Get_Trx());
if (ds1 != null && ds1.Tables[0].Rows.Count > 0)
{
return(Msg.GetMsg(GetCtx(), "VIS_RecordsAlreadyGenerated"));
}
// Get currentNext for generating key
C_ProfitLossLines_ID = MSequence.GetNextID(GetAD_Client_ID(), "C_ProfitLossLines", Get_Trx());
C_ProfitLossLines_ID -= 1;
// Delete All Record from line
DB.ExecuteQuery("DELETE FROM C_ProfitLossLines WHERE C_ProfitLoss_ID=" + GetRecord_ID());
insert.Clear();
insert.Append(@"INSERT INTO C_ProfitLossLines (C_ProfitLossLines_ID , AD_Client_ID , AD_Org_ID , C_ProfitLoss_ID , C_ProfitAndLoss_ID , C_AcctSchema_ID , PostingType ,
AccountCredit ,AccountDebit,Account_ID, C_SubAcct_ID, C_BPartner_ID , M_Product_ID , C_Project_ID , C_SalesRegion_ID , C_Campaign_ID , AD_OrgTrx_ID ,
C_LocFrom_ID , C_LocTo_ID , C_Activity_ID, User1_ID , User2_ID , UserElement1_ID, UserElement2_ID, UserElement3_ID, UserElement4_ID,
UserElement5_ID, UserElement6_ID, UserElement7_ID, UserElement8_ID, UserElement9_ID , GL_Budget_ID, C_ProjectPhase_ID, C_ProjectTask_ID, LedgerCode,LedgerName, Line ) ");
qry.Clear();
qry.Append(@"select " + C_ProfitLossLines_ID + " + rownum AS C_ProfitLossLines_id, ft.AD_Client_ID , ft.AD_Org_ID , " + PL.GetC_ProfitLoss_ID() + " , " + prof.GetC_ProfitAndLoss_ID() + ", ft.C_AcctSchema_ID,ft.PostingType,ft.AmtAcctDr,ft.AmtAcctCr,ft.Account_ID,ft.C_SubAcct_ID,ft.C_BPartner_ID,ft.M_Product_ID,ft.C_Project_ID,ft.C_SalesRegion_ID,ft.C_Campaign_ID,ft.AD_OrgTrx_ID,ft.C_LocFrom_ID,ft.C_LocTo_ID,ft.C_Activity_ID,ft.User1_ID,ft.User2_ID,ft.UserElement1_ID,ft.UserElement2_ID,"
+ " ft.UserElement3_ID,ft.UserElement4_ID, ft.UserElement5_ID, ft.UserElement6_ID, ft.UserElement7_ID,ft.UserElement8_ID, ft.UserElement9_ID,ft.GL_Budget_ID,ft.C_ProjectPhase_ID,ft.C_ProjectTask_ID,"
+ @" ev.Value as LedgerCode,ev.Name as LedgerName , (SELECT NVL(MAX(Line),0) FROM C_ProfitLossLines WHERE C_ProfitLoss_ID=" + PL.GetC_ProfitLoss_ID() + " ) + (rownum *10) AS lineno from Fact_Acct ft inner join c_elementvalue ev on ft.account_id=ev.c_elementvalue_id where ft.ad_client_id= " + GetAD_Client_ID());
// Added by SUkhwinder on 27 Nov 2017, for filtering query on the basis of postingtype. And string variable converted to stringBuilder also.
if (prof.Get_Value("PostingType") != null)
{
qry.Append(" and ft.PostingType = '" + prof.Get_Value("PostingType") + "'");
}
qry.Append(" and ft.DateAcct >=" + GlobalVariable.TO_DATE(prof.GetDateFrom(), true) + " AND ft.DateAcct <= " + GlobalVariable.TO_DATE(prof.GetDateTo(), true) + " AND (ev.accounttype='E' OR ev.accounttype='R') and ev.isintermediatecode='N'"
+ " AND ft.AD_Org_ID IN ( (SELECT Ad_Org_ID FROM AD_Org WHERE isactive = 'Y' AND (legalentityorg =" + PL.GetAD_Org_ID() + " OR Ad_Org_ID = " + PL.GetAD_Org_ID() + ")))");
if (Util.GetValueOfInt(PL.Get_Value("C_AcctSchema_ID")) > 0)
{
qry.Append(" AND ft.C_AcctSchema_ID=" + Util.GetValueOfInt(PL.Get_Value("C_AcctSchema_ID")));
}
insert.Append(qry.ToString());
int no = Util.GetValueOfInt(DB.ExecuteQuery(insert.ToString(), null, Get_Trx()));
if (no > 0)
{
// Update curentNext in AD_Sequence
C_ProfitLossLines_ID += (no + 1);
String updateSQL = "UPDATE AD_Sequence SET CurrentNext = " + C_ProfitLossLines_ID + ", CurrentNextSys = " + C_ProfitLossLines_ID + " "
+ " WHERE Upper(Name)=Upper('C_ProfitLossLines')"
+ " AND IsActive='Y' AND IsTableID='Y' AND IsAutoSequence='Y' ";
DB.ExecuteQuery(updateSQL, null, Get_Trx());
// Update Profit Before tax on Header
ProfitBeforeTax = Util.GetValueOfDecimal(DB.ExecuteScalar("SELECT SUM(AccountDebit) - SUM(AccountCredit) FROM C_ProfitLossLines WHERE C_ProfitAndLoss_ID > 0 AND IsActive='Y' AND C_ProfitLoss_ID=" + GetRecord_ID(), null, Get_Trx()));
prof.SetProfitBeforeTax(ProfitBeforeTax);
if (!prof.Save(Get_Trx()))
{
ds.Dispose();
Rollback();
// return GetReterivedError(prof, "ProfitNotSaved");
return(Msg.GetMsg(GetCtx(), "ProfitNotSaved"));
}
else
{
Get_Trx().Commit();
// insert record against Income summary acct
InsertProfitLossLine(prof);
}
return(Msg.GetMsg(GetCtx(), "LinesGenerated"));
}
else
{
return(Msg.GetMsg(GetCtx(), "RecordNoFound"));
}
}
}
/// <summary>
/// Add Or Update Business Partner
/// </summary>
/// <param name="searchKey"></param>
/// <param name="name"></param>
/// <param name="name2"></param>
/// <param name="greeting"></param>
/// <param name="bpGroup"></param>
/// <param name="bpRelation"></param>
/// <param name="bpLocation"></param>
/// <param name="contact"></param>
/// <param name="greeting1"></param>
/// <param name="title"></param>
/// <param name="email"></param>
/// <param name="address"></param>
/// <param name="phoneNo"></param>
/// <param name="phoneNo2"></param>
/// <param name="fax"></param>
/// <param name="ctx"></param>
/// <param name="_windowNo"></param>
/// <param name="BPtype"></param>
/// <param name="C_BPartner_ID"></param>
/// <returns></returns>
public string AddBPartner(string searchKey, string name, string name2, string greeting, string bpGroup, string bpRelation, string bpLocation, string contact, string greeting1, string title, string email, string address, string phoneNo, string phoneNo2, string fax, Ctx ctx, int _windowNo, string BPtype, int C_BPartner_ID, bool isCustomer, bool isVendor, bool isProspect, string fileUrl, string umobile, string webUrl, bool isEmployee)
{
StringBuilder strError = new StringBuilder();
int AD_Client_ID = ctx.GetAD_Client_ID();
if (C_BPartner_ID > 0)
{
_partner = new MBPartner(ctx, C_BPartner_ID, null);
}
else
{
_partner = MBPartner.GetTemplate(ctx, AD_Client_ID);
}
bool isSOTrx = ctx.IsSOTrx(_windowNo);
_partner.SetIsCustomer(isSOTrx);
_partner.SetIsVendor(!isSOTrx);
// JID_1197 IN Business partner updating Createdby,Updatedby,Created,Updated fields as per changed date
_partner.Set_ValueNoCheck("CreatedBy", ctx.GetAD_User_ID());
_partner.Set_ValueNoCheck("Created", DateTime.Now);
_partner.Set_ValueNoCheck("Updated", DateTime.Now);
_partner.Set_ValueNoCheck("UpdatedBy", ctx.GetAD_User_ID());
if (BPtype != null && (!isCustomer && !isVendor))
{
if (BPtype.Contains("Customer"))
{
_partner.SetIsCustomer(true);
}
if (BPtype.Contains("Employee"))
{
_partner.SetIsEmployee(true);
}
if (BPtype.Contains("Vendor"))
{
_partner.SetIsVendor(true);
}
if (BPtype.Contains("Prospect"))
{
_partner.SetIsProspect(true);
}
/*
* if (BPtype == "Customer")
* {
* _partner.SetIsCustomer(true);
* }
* else if (BPtype == "Employee")
* {
* _partner.SetIsEmployee(true);
* }
* else if (BPtype == "Vendor")
* {
* _partner.SetIsVendor(true);
* }
* else if (BPtype == "Prospect")
* {
* _partner.SetIsProspect(true);
* }*/
}
if (isCustomer)
{
_partner.SetIsCustomer(true);
}
else
{
_partner.SetIsCustomer(false);
}
if (isVendor)
{
_partner.SetIsVendor(true);
}
else
{
_partner.SetIsVendor(false);
}
if (isProspect)
{
_partner.SetIsProspect(true);
}
else
{
_partner.SetIsProspect(false);
}
if (isEmployee)
{
_partner.SetIsEmployee(true);
}
else
{
_partner.SetIsEmployee(false);
}
if (searchKey == null || searchKey.Length == 0)
{
// get Table Documet No
searchKey = MSequence.GetDocumentNo(ctx.GetAD_Client_ID(), "C_BPartner", null, ctx);
//Dispatcher.BeginInvoke(() => { txtValue.Text = value; });
}
_partner.SetValue(searchKey);
//
_partner.SetName(name);
_partner.SetURL(webUrl);
// _partner.SetName2(name2);
//KeyNamePair p = (KeyNamePair)cmbGreetingBP.SelectedItem;
//if (greeting >0)
//{
// _partner.SetC_Greeting_ID(greeting);
//}
//else
//{
// _partner.SetC_Greeting_ID(0);
//}
if (greeting != string.Empty)
{
_partner.SetC_Greeting_ID(Convert.ToInt32(greeting));
}
else
{
_partner.SetC_Greeting_ID(0);
}
/***************************************************/
_partner.SetC_BP_Group_ID(Util.GetValueOfInt(bpGroup));
/***************************************************/
if (_partner.Save())
{
log.Fine("C_BPartner_ID=" + _partner.GetC_BPartner_ID());
}
else
{
// Classes.ShowMessage.Error("SearchKeyExist", null);
strError.Append("SearchKeyExist");
//this.Cursor = Cursors.Arrow;
return(strError.ToString());
}
// ***** Business Partner - Location *****
if (_pLocation == null)
{
if (C_BPartner_ID > 0)
{
_pLocation = new MBPartnerLocation(ctx, GetBPartnerLocationID(_partner.Get_ID()), null);
if (_pLocation.Get_ID() <= 0)
{
_pLocation = new MBPartnerLocation(_partner);
}
}
else
{
_pLocation = new MBPartnerLocation(_partner);
}
}
if (address != string.Empty)
{
_pLocation.SetC_Location_ID(Convert.ToInt32(address));
}
//
_pLocation.SetPhone(phoneNo);
// _pLocation.SetPhone2(phoneNo2);
_pLocation.SetFax(fax);
if (_pLocation.Save())
{
log.Fine("C_BPartner_Location_ID=" + _pLocation.GetC_BPartner_Location_ID());
}
else
{
// ADialog.error(m_WindowNo, this, "BPartnerNotSaved", Msg.translate(Env.getCtx(), "C_BPartner_Location_ID"));
// Classes.ShowMessage.Error("BPartnerNotSaved", null);
//this.Cursor = Cursors.Arrow;
strError.Append("BPartnerNotSaved");
return(strError.ToString());
}
// ***** Business Partner - User *****
//String contact = txtContact.Text;
//String email = txtEMail.Text;
if (_user == null && (contact.Length > 0 || email.Length > 0))
{
if (C_BPartner_ID > 0)
{
_user = new MUser(ctx, GetUserID(_partner.Get_ID()), null);
}
else
{
_user = new MUser(_partner);
}
}
if (_user != null)
{
if (contact.Length == 0)
{
contact = name;
}
_user.SetName(contact);
_user.SetEMail(email);
_user.SetTitle(title);
_user.SetC_Location_ID(Convert.ToInt32(address));
// = (KeyNamePair)cmbGreetingC.SelectedItem;
//if (greeting1 >0)
// _user.SetC_Greeting_ID(greeting1);
if (greeting1 != string.Empty)
{
_user.SetC_Greeting_ID(Convert.ToInt32(greeting1));
}
else
{
_user.SetC_Greeting_ID(0);
}
//
_user.SetPhone(phoneNo);
// _user.SetPhone2(phoneNo2);
_user.SetMobile(umobile);
_user.SetFax(fax);
_user.SetC_BPartner_Location_ID(_pLocation.GetC_BPartner_Location_ID());
if (_user.Save())
{
if (fileUrl != null && fileUrl != string.Empty)
{
_user.SetAD_Image_ID(SaveUserImage(ctx, fileUrl, _user.GetAD_User_ID()));
}
if (_user.Save())
{
log.Fine("AD_User_ID(AD_Image_ID)=" + _user.GetAD_User_ID() + "(" + _user.GetAD_Image_ID() + ")");
}
log.Fine("AD_User_ID=" + _user.GetAD_User_ID());
}
else
{
//Classes.ShowMessage.Error("BPartnerNotSaved", null);
//this.Cursor = Cursors.Arrow;
strError.Append("BPartnerNotSaved");
return(strError.ToString());
}
/*************************************************/
if ((bpRelation != null && bpLocation != null) && (bpRelation != string.Empty && bpLocation != string.Empty))
{
if (bpRelation.ToString().Trim() == "" || bpLocation.ToString().Trim() == "")
{
int dele = DB.ExecuteQuery("DELETE from C_BP_Relation where c_bpartner_id=" + _partner.GetC_BPartner_ID(), null, null);
if (dele == -1)
{
log.SaveError("C_BP_RelationNotDeleted", "c_bpartner_id=" + _partner.GetC_BPartner_ID());
}
}
else
{
//Business Partner Relation
if (C_BPartner_ID > 0)
{
_bprelation = new X_C_BP_Relation(ctx, GetBPRelationID(_partner.Get_ID()), null);
}
else
{
_bprelation = new X_C_BP_Relation(ctx, 0, null);
}
_bprelation.SetAD_Client_ID(_partner.GetAD_Client_ID());
_bprelation.SetAD_Org_ID(_partner.GetAD_Org_ID());
_bprelation.SetName(_partner.GetName());
_bprelation.SetDescription(_partner.GetDescription());
_bprelation.SetC_BPartner_ID(_partner.GetC_BPartner_ID());
_bprelation.SetC_BPartner_Location_ID(_pLocation.GetC_BPartner_Location_ID());
_bprelation.SetC_BP_Relation_ID(Util.GetValueOfInt(bpRelation));
_bprelation.SetC_BPartnerRelation_ID(Util.GetValueOfInt(bpRelation));
_bprelation.SetC_BPartnerRelation_Location_ID(Util.GetValueOfInt(bpLocation));
_bprelation.SetIsBillTo(true);
if (_bprelation.Save())
{
log.Fine("C_BP_Relation_ID=" + _bprelation.GetC_BP_Relation_ID());
}
else
{
//Classes.ShowMessage.Error("BPRelationNotSaved", null);
//this.Cursor = Cursors.Arrow;
strError.Append("BPRelationNotSaved");
return(strError.ToString());
}
}
}
/*************************************************/
}
return(strError.ToString());
}
} // checkTableSequences
/// <summary>
/// Check Table Sequence ID values
/// </summary>
/// <param name="ctx">context</param>
/// <param name="sp">server process or null</param>
private static void CheckTableID(Ctx ctx, SvrProcess sp)
{
if (MSysConfig.IsNativeSequence(false))
{
String sql = "SELECT * FROM AD_Sequence "
+ "WHERE IsTableID='Y' "
+ "ORDER BY Name";
DataSet ds = null;
int counter = 0;
Trx trxName = null;
if (sp != null)
{
trxName = sp.Get_Trx();
}
try
{
ds = DataBase.DB.ExecuteDataset(sql, null, trxName);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
//Get Max value from Table_ID or AD_Sequence and update.
sql = @"Update AD_Sequence set updatedby=" + ctx.GetAD_User_ID() + ", updated=getdate(), CurrentNext=(SELECT max(maximum) FROM ( SELECT max(" + ds.Tables[0].Rows[i]["Name"].ToString() + @"_ID)+1 as maximum from " + ds.Tables[0].Rows[i]["Name"].ToString() +
@" Union
SELECT currentnext AS maximum FROM AD_Sequence WHERE Name = '" + ds.Tables[0].Rows[i]["Name"].ToString() + "') ";
if (DB.IsPostgreSQL())
{
sql += " foo ";
}
sql += ") WHERE Name = '" + ds.Tables[0].Rows[i]["Name"].ToString() + "'";
int curVal = DB.ExecuteQuery(sql, null, trxName);
if (curVal > 0)
{
sp.AddLog(0, null, null, "Sequence Updated For :" + ds.Tables[0].Rows[i]["Name"].ToString());
counter++;
}
else
{
_log.Severe("Sequence Not Updated For :" + ds.Tables[0].Rows[i]["Name"].ToString());
}
}
}
catch (Exception ex)
{
_log.Log(Level.SEVERE, sql, ex);
}
}
else
{
int IDRangeEnd = DataBase.DB.GetSQLValue(null,
"SELECT IDRangeEnd FROM AD_System");
if (IDRangeEnd <= 0)
{
IDRangeEnd = DataBase.DB.GetSQLValue(null,
"SELECT MIN(IDRangeStart)-1 FROM AD_Replication");
}
_log.Info("IDRangeEnd = " + IDRangeEnd);
//
String sql = "SELECT * FROM AD_Sequence "
+ "WHERE IsTableID='Y' "
+ "ORDER BY Name";
int counter = 0;
//IDataReader idr = null;
DataSet ds = null;
Trx trxName = null;
if (sp != null)
{
trxName = sp.Get_Trx();
}
try
{
//pstmt = DataBase.prepareStatement(sql, trxName);
//idr = DataBase.DB.ExecuteReader(sql, null, trxName);
ds = DataBase.DB.ExecuteDataset(sql, null, trxName);
//while (idr.Read())
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
MSequence seq = new MSequence(ctx, ds.Tables[0].Rows[i], trxName);
int old = seq.GetCurrentNext();
int oldSys = seq.GetCurrentNextSys();
if (seq.ValidateTableIDValue())
{
if (seq.GetCurrentNext() != old)
{
String msg = seq.GetName() + " ID "
+ old + " -> " + seq.GetCurrentNext();
if (sp != null)
{
sp.AddLog(0, null, null, msg);
}
else
{
_log.Fine(msg);
}
}
if (seq.GetCurrentNextSys() != oldSys)
{
String msg = seq.GetName() + " Sys "
+ oldSys + " -> " + seq.GetCurrentNextSys();
if (sp != null)
{
sp.AddLog(0, null, null, msg);
}
else
{
_log.Fine(msg);
}
}
if (seq.Save())
{
counter++;
}
else
{
_log.Severe("Not updated: " + seq);
}
}
// else if (CLogMgt.isLevel(6))
// log.fine("OK - " + tableName);
}
// idr.Close();
}
catch (Exception e)
{
//if (idr != null)
//{
// idr.Close();
//}
_log.Log(Level.SEVERE, sql, e);
}
_log.Fine("#" + counter);
}
} // checkTableID
} // checkTableSequences
/// <summary>
/// Check Table Sequence ID values
/// </summary>
/// <param name="ctx">context</param>
/// <param name="sp">server process or null</param>
private static void CheckTableID(Ctx ctx, SvrProcess sp)
{
int IDRangeEnd = DataBase.DB.GetSQLValue(null,
"SELECT IDRangeEnd FROM AD_System");
if (IDRangeEnd <= 0)
{
IDRangeEnd = DataBase.DB.GetSQLValue(null,
"SELECT MIN(IDRangeStart)-1 FROM AD_Replication");
}
_log.Info("IDRangeEnd = " + IDRangeEnd);
//
String sql = "SELECT * FROM AD_Sequence "
+ "WHERE IsTableID='Y' "
+ "ORDER BY Name";
int counter = 0;
IDataReader idr = null;
Trx trxName = null;
if (sp != null)
{
trxName = sp.Get_Trx();
}
try
{
//pstmt = DataBase.prepareStatement(sql, trxName);
idr = DataBase.DB.ExecuteReader(sql, null, trxName);
while (idr.Read())
{
MSequence seq = new MSequence(ctx, idr, trxName);
int old = seq.GetCurrentNext();
int oldSys = seq.GetCurrentNextSys();
if (seq.ValidateTableIDValue())
{
if (seq.GetCurrentNext() != old)
{
String msg = seq.GetName() + " ID "
+ old + " -> " + seq.GetCurrentNext();
if (sp != null)
{
sp.AddLog(0, null, null, msg);
}
else
{
_log.Fine(msg);
}
}
if (seq.GetCurrentNextSys() != oldSys)
{
String msg = seq.GetName() + " Sys "
+ oldSys + " -> " + seq.GetCurrentNextSys();
if (sp != null)
{
sp.AddLog(0, null, null, msg);
}
else
{
_log.Fine(msg);
}
}
if (seq.Save())
{
counter++;
}
else
{
_log.Severe("Not updated: " + seq);
}
}
// else if (CLogMgt.isLevel(6))
// log.fine("OK - " + tableName);
}
idr.Close();
}
catch (Exception e)
{
if (idr != null)
{
idr.Close();
}
_log.Log(Level.SEVERE, sql, e);
}
_log.Fine("#" + counter);
} // checkTableID
} // validate
/// <summary>
/// Check existence of Table Sequences.
/// </summary>
/// <param name="ctx">context</param>
/// <param name="sp">server process or null</param>
private static void CheckTableSequences(Ctx ctx, SvrProcess sp)
{
Trx trxName = null;
if (sp != null)
{
trxName = sp.Get_Trx();
}
String sql = "SELECT TableName "
+ "FROM AD_Table t "
+ "WHERE IsActive='Y' AND IsView='N'"
+ " AND NOT EXISTS (SELECT * FROM AD_Sequence s "
+ "WHERE UPPER(s.Name)=UPPER(t.TableName) AND s.IsTableID='Y')";
IDataReader idr = null;
try
{
//pstmt = DataBase.prepareStatement(sql, trxName);
idr = DataBase.DB.ExecuteReader(sql, null, trxName);
while (idr.Read())
{
String tableName = Utility.Util.GetValueOfString(idr[0]);// rs.getString(1);
if (MSequence.CreateTableSequence(ctx, tableName, trxName))
{
if (sp != null)
{
sp.AddLog(0, null, null, tableName);
}
else
{
_log.Fine(tableName);
}
}
else
{
idr.Close();
throw new Exception("Error creating Table Sequence for " + tableName);
}
}
idr.Close();
}
catch (Exception e)
{
if (idr != null)
{
idr.Close();
}
_log.Log(Level.SEVERE, sql, e);
}
// Sync Table Name case
//jz replace s with AD_Sequence
sql = "UPDATE AD_Sequence "
+ "SET Name = (SELECT TableName FROM AD_Table t "
+ "WHERE t.IsView='N' AND UPPER(AD_Sequence.Name)=UPPER(t.TableName)) "
+ "WHERE AD_Sequence.IsTableID='Y'"
+ " AND EXISTS (SELECT * FROM AD_Table t "
+ "WHERE t.IsActive='Y' AND t.IsView='N'"
+ " AND UPPER(AD_Sequence.Name)=UPPER(t.TableName) AND AD_Sequence.Name<>t.TableName)";
int no = DataBase.DB.ExecuteQuery(sql, null, trxName);// DataBase.executeUpdate(sql, trxName);
if (no > 0)
{
if (sp != null)
{
sp.AddLog(0, null, null, "SyncName #" + no);
}
else
{
_log.Fine("Sync #" + no);
}
}
if (no >= 0)
{
return;
}
/** Find Duplicates */
sql = "SELECT TableName, s.Name "
+ "FROM AD_Table t, AD_Sequence s "
+ "WHERE t.IsActive='Y' AND t.IsView='N'"
+ " AND UPPER(s.Name)=UPPER(t.TableName) AND s.Name<>t.TableName";
//
try
{
//pstmt = DataBase.prepareStatement (sql, null);
idr = DataBase.DB.ExecuteReader(sql, null, trxName);
while (idr.Read())
{
String TableName = Utility.Util.GetValueOfString(idr[0]);// rs.getString(1);
String SeqName = Utility.Util.GetValueOfString(idr[1]);
sp.AddLog(0, null, null, "ERROR: TableName=" + TableName + " - Sequence=" + SeqName);
}
idr.Close();
}
catch (Exception e)
{
if (idr != null)
{
idr.Close();
}
_log.Log(Level.SEVERE, sql, e);
}
} // checkTableSequences
} // checkTableSequences
/// <summary>
/// Check Table Sequence ID values
/// </summary>
/// <param name="ctx">context</param>
/// <param name="sp">server process or null</param>
private static void CheckTableID(Ctx ctx, SvrProcess sp)
{
if (MSysConfig.IsNativeSequence(false))
{
String sql = "SELECT * FROM AD_Sequence "
+ "WHERE IsTableID='Y' "
+ "ORDER BY Name";
DataSet ds = null;
int counter = 0;
Trx trxName = null;
if (sp != null)
{
trxName = sp.Get_Trx();
}
try
{
ds = DataBase.DB.ExecuteDataset(sql, null, trxName);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
MSequence seq = new MSequence(ctx, ds.Tables[0].Rows[i], trxName);
int curVal = DB.GetSQLValue(trxName, "select " + ds.Tables[0].Rows[i]["Name"].ToString() + "_SEQ.currval+1 from DUAL");
seq.SetCurrentNext(curVal);
if (seq.Save())
{
sp.AddLog(0, null, null, "Sequence Updated For :" + ds.Tables[0].Rows[i]["Name"].ToString());
counter++;
}
else
{
_log.Severe("Sequence Not Updated For :" + ds.Tables[0].Rows[i]["Name"].ToString());
}
}
}
catch (Exception ex)
{
_log.Log(Level.SEVERE, sql, ex);
}
}
else
{
int IDRangeEnd = DataBase.DB.GetSQLValue(null,
"SELECT IDRangeEnd FROM AD_System");
if (IDRangeEnd <= 0)
{
IDRangeEnd = DataBase.DB.GetSQLValue(null,
"SELECT MIN(IDRangeStart)-1 FROM AD_Replication");
}
_log.Info("IDRangeEnd = " + IDRangeEnd);
//
String sql = "SELECT * FROM AD_Sequence "
+ "WHERE IsTableID='Y' "
+ "ORDER BY Name";
int counter = 0;
//IDataReader idr = null;
DataSet ds = null;
Trx trxName = null;
if (sp != null)
{
trxName = sp.Get_Trx();
}
try
{
//pstmt = DataBase.prepareStatement(sql, trxName);
//idr = DataBase.DB.ExecuteReader(sql, null, trxName);
ds = DataBase.DB.ExecuteDataset(sql, null, trxName);
//while (idr.Read())
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
MSequence seq = new MSequence(ctx, ds.Tables[0].Rows[i], trxName);
int old = seq.GetCurrentNext();
int oldSys = seq.GetCurrentNextSys();
if (seq.ValidateTableIDValue())
{
if (seq.GetCurrentNext() != old)
{
String msg = seq.GetName() + " ID "
+ old + " -> " + seq.GetCurrentNext();
if (sp != null)
{
sp.AddLog(0, null, null, msg);
}
else
{
_log.Fine(msg);
}
}
if (seq.GetCurrentNextSys() != oldSys)
{
String msg = seq.GetName() + " Sys "
+ oldSys + " -> " + seq.GetCurrentNextSys();
if (sp != null)
{
sp.AddLog(0, null, null, msg);
}
else
{
_log.Fine(msg);
}
}
if (seq.Save())
{
counter++;
}
else
{
_log.Severe("Not updated: " + seq);
}
}
// else if (CLogMgt.isLevel(6))
// log.fine("OK - " + tableName);
}
// idr.Close();
}
catch (Exception e)
{
//if (idr != null)
//{
// idr.Close();
//}
_log.Log(Level.SEVERE, sql, e);
}
_log.Fine("#" + counter);
}
} // checkTableID