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> /// 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); }
//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")); } } }