/// <summary> /// Process Remote /// @ejb.interface-method view-type="both" /// </summary> /// <param name="ctx">Context</param> /// <param name="pi">Process Info</param> /// <returns>resulting Process Info</returns> public ProcessInfo Process(Ctx ctx, ProcessInfo pi) { String className = pi.GetClassName(); log.Info(className + " - " + pi); _processCount++; // Get Class // Class clazz = null; // XDoclet: no generics! Type clazz = null; try { //clazz = Class.forName (className); clazz = Type.GetType(className); } catch (Exception ex) { log.Log(Level.WARNING, className, ex); pi.SetSummary("ClassNotFound", true); return(pi); } // Get Process SvrProcess process = null; try { //process = (SvrProcess)clazz.newInstance (); process = (SvrProcess)Activator.CreateInstance(clazz); } catch (Exception ex) { log.Log(Level.WARNING, "Instance for " + className, ex); pi.SetSummary("InstanceError", true); return(pi); } // Start Process Trx trx = Trx.Get("ServerPrc"); try { bool ok = process.StartProcess(ctx, pi, trx); pi = process.GetProcessInfo(); trx.Commit(); trx.Close(); } catch (Exception ex1) { trx.Rollback(); trx.Close(); pi.SetSummary("ProcessError=>" + ex1.Message, true); return(pi); } return(pi); }
public static void CreateDocumentTypes(Ctx ctx, int AD_Client_ID, SvrProcess sp, Trx trxName) { _log.Info("AD_Client_ID=" + AD_Client_ID); String sql = "SELECT rl.Value, rl.Name " + "FROM AD_Ref_List rl " + "WHERE rl.AD_Reference_ID=183" + " AND rl.IsActive='Y' AND NOT EXISTS " + " (SELECT * FROM C_DocType dt WHERE dt.AD_Client_ID='" + AD_Client_ID + "' AND rl.Value=dt.DocBaseType)"; IDataReader idr = null; try { idr = DataBase.DB.ExecuteReader(sql, null); while (idr.Read()) { String name = idr[1].ToString(); String value = idr[0].ToString(); _log.Config(name + "=" + value); MDocType dt = new MDocType(ctx, value, name, trxName); if (dt.Save()) { if (sp != null) { sp.AddLog(0, null, null, name); } else { _log.Fine(name); } } else { if (sp != null) { sp.AddLog(0, null, null, "Not created: " + name); } else { _log.Warning("Not created: " + name); } } } idr.Close(); } catch (Exception e) { if (idr != null) { idr.Close(); } _log.Log(Level.SEVERE, sql, e); } } // createDocumentTypes
} // 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
} // 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++) { //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) { 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
} // 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
} // createDocumentTypes /// <summary> /// /// </summary> /// <param name="ctx"></param> /// <param name="AD_Client_ID"></param> /// <param name="sp"></param> /// <param name="trxName"></param> public static void CreatePeriodControls(Ctx ctx, int AD_Client_ID, SvrProcess sp, Trx trxName) { _log.Info("AD_Client_ID=" + AD_Client_ID); // Delete Duplicates //jz remove correlation ID String sql = "DELETE FROM C_PeriodControl pc1 " String sql = "DELETE FROM C_PeriodControl " + "WHERE (C_Period_ID, DocBaseType) IN " + "(SELECT C_Period_ID, DocBaseType " + "FROM C_PeriodControl pc2 " + "GROUP BY C_Period_ID, DocBaseType " + "HAVING COUNT(*) > 1)" + " AND C_PeriodControl_ID NOT IN " + "(SELECT MIN(C_PeriodControl_ID) " + "FROM C_PeriodControl pc3 " + "GROUP BY C_Period_ID, DocBaseType)"; int no = DataBase.DB.ExecuteQuery(sql, null, trxName); _log.Info("Duplicates deleted #" + no); // Insert Missing sql = "SELECT DISTINCT p.AD_Client_ID, p.AD_Org_ID, p.C_Period_ID, dt.DocBaseType " + "FROM C_Period p" + " FULL JOIN C_DocType dt ON (p.AD_Client_ID=dt.AD_Client_ID) " + "WHERE p.AD_Client_ID='" + AD_Client_ID + "'" + " AND NOT EXISTS" + " (SELECT * FROM C_PeriodControl pc " + "WHERE pc.C_Period_ID=p.C_Period_ID AND pc.DocBaseType=dt.DocBaseType)"; IDataReader idr = null; int counter = 0; try { idr = DataBase.DB.ExecuteReader(sql, null, trxName); while (idr.Read()) { int Client_ID = Utility.Util.GetValueOfInt(idr[0].ToString()); int Org_ID = Utility.Util.GetValueOfInt(idr[1].ToString()); // Get Organization from Period int C_Period_ID = Utility.Util.GetValueOfInt(idr[2].ToString()); String DocBaseType = idr[3].ToString(); _log.Config("AD_Client_ID=" + Client_ID + ", C_Period_ID=" + C_Period_ID + ", DocBaseType=" + DocBaseType); // MPeriodControl pc = new MPeriodControl(ctx, Client_ID, C_Period_ID, DocBaseType, trxName); pc.SetAD_Org_ID(Org_ID); // Set Organization of Period, on Period Control. if (pc.Save()) { counter++; _log.Fine(pc.ToString()); } else { _log.Warning("Not saved: " + pc); } } idr.Close(); } catch (Exception e) { if (idr != null) { idr.Close(); } _log.Log(Level.SEVERE, sql, e); } if (sp != null) { sp.AddLog(0, null, (Decimal)counter, "@C_PeriodControl_ID@ @Created@"); } _log.Info("Inserted #" + counter); } // createPeriodControls
/** * Update Balance of Client * @param ctx context * @param deleteFirst delete first * @param dateFrom null for all or first date to delete/calculate * @param trx transaction * @param svrPrc optional server process * @return Info */ public static String UpdateBalanceClient(Ctx ctx, DateTime?dateFrom, Trx trx, int Fact_Accumulation_ID, SvrProcess svrPrc) { int AD_Client_ID = ctx.GetAD_Client_ID(); StringBuilder Info = new StringBuilder(); MAcctSchema[] ass = MAcctSchema.GetClientAcctSchema(ctx, AD_Client_ID); foreach (MAcctSchema as1 in ass) { if (Info.Length > 0) { Info.Append(" - "); } String msg = UpdateBalance(ctx, as1.GetC_AcctSchema_ID(), dateFrom, trx, Fact_Accumulation_ID, svrPrc); Info.Append(as1.GetName()).Append(":").Append(msg); } return(Info.ToString()); }
/** * Update / Create Balances. * Called from FinReport, FactAcctReset (indirect) * @param AD_Client_ID client * @param C_AcctSchema_ID accounting schema 0 for all * @param deleteFirst delete balances first * @param dateFrom null for all or first date to delete/calculate * @param trx transaction * @param svrPrc optional server process * @return Message to be translated */ public static String UpdateBalance(Ctx ctx, int C_AcctSchema_ID, DateTime?dateFrom, Trx trxp, int Fact_Accumulation_ID, SvrProcess svrPrc) { Trx trx = trxp; _log.Info("C_AcctSchema_ID=" + C_AcctSchema_ID + "DateFrom=" + dateFrom); long start = CommonFunctions.CurrentTimeMillis(); //List<Object> param = new List<Object>(); List <MFactAccumulation> accums = null; int no = 0; if (Fact_Accumulation_ID == 0) { accums = MFactAccumulation.GetAll(ctx, C_AcctSchema_ID); if (accums.Count == 0) { // Create a Balance aggregation of type Daily. MFactAccumulation defaultAccum = new MFactAccumulation(ctx, 0, trx); defaultAccum.SetAD_Client_ID(ctx.GetAD_Client_ID()); defaultAccum.SetAD_Org_ID(ctx.GetAD_Org_ID()); defaultAccum.SetC_AcctSchema_ID(C_AcctSchema_ID); defaultAccum.SetBALANCEACCUMULATION(X_Fact_Accumulation.BALANCEACCUMULATION_Daily); defaultAccum.SetIsActive(true); defaultAccum.SetIsDefault(true); defaultAccum.SetISACTIVITY(true); defaultAccum.SetISBUDGET(true); defaultAccum.SetISBUSINESSPARTNER(true); defaultAccum.SetISCAMPAIGN(true); defaultAccum.SetISLOCATIONFROM(true); defaultAccum.SetISLOCATIONTO(true); defaultAccum.SetISPRODUCT(true); defaultAccum.SetISPROJECT(true); defaultAccum.SetISSALESREGION(true); defaultAccum.SetISUSERLIST1(true); defaultAccum.SetISUSERLIST2(true); defaultAccum.SetISUSERELEMENT1(true); defaultAccum.SetISUSERELEMENT2(true); if (!defaultAccum.Save(trx)) { _log.Log(Level.SEVERE, "Unable to create Default Balance Aggregation"); return("Unable to create Default Balance Aggregation"); } else { accums.Add(defaultAccum); } } } else { MFactAccumulation selectAccum = new MFactAccumulation(ctx, Fact_Accumulation_ID, trx); accums = new List <MFactAccumulation>(); accums.Add(selectAccum); } foreach (MFactAccumulation accum in accums) { // dateFrom = MFactAccumulation.GetDateFrom(accum, dateFrom); dateFrom = accum.GetDateFrom(); String type = accum.GetBALANCEACCUMULATION(); String trunc = null; if (X_Fact_Accumulation.BALANCEACCUMULATION_Daily.Equals(type)) { trunc = TimeUtil.TRUNC_DAY; } else if (X_Fact_Accumulation.BALANCEACCUMULATION_CalendarWeek.Equals(type)) { trunc = TimeUtil.TRUNC_WEEK; } else if (X_Fact_Accumulation.BALANCEACCUMULATION_CalendarMonth.Equals(type)) { trunc = TimeUtil.TRUNC_MONTH; } if (X_Fact_Accumulation.BALANCEACCUMULATION_PeriodOfAViennaCalendar.Equals(type) && !CheckPeriod(accum, dateFrom)) { _log.Log(Level.SEVERE, "Necessary Period doesn't exist for the calendar"); return("Necessary Period doesn't exist for the calendar"); } String dateClause = null; if (X_Fact_Accumulation.BALANCEACCUMULATION_PeriodOfAViennaCalendar.Equals(type)) { dateClause = " Period.StartDate "; } else { dateClause = " a.DateAcct "; _log.Fine(trunc); //dateClause = " TRUNC(a.DateAcct, '" + trunc + "' ) "; } // Delete the balances DeleteBalance(ctx.GetAD_Client_ID(), C_AcctSchema_ID, dateFrom, trx, accum.GetFACT_ACCUMULATION_ID(), svrPrc); /** Insert **/ //param = new List<Object>(); String insert = "INSERT INTO Fact_Acct_Balance " + "(AD_Client_ID, AD_Org_ID, AD_OrgTrx_ID, C_AcctSchema_ID, DateAcct," + " Account_ID, PostingType, Fact_Accumulation_ID, M_Product_ID, C_BPartner_ID," + " C_Project_ID, C_SalesRegion_ID,C_Activity_ID," + " C_Campaign_ID, C_LocTo_ID, C_LocFrom_ID, User1_ID, User2_ID, GL_Budget_ID," + " UserElement1_ID, UserElement2_ID, " + " AmtAcctDr, AmtAcctCr, Qty) "; String select = " SELECT AD_Client_ID, AD_Org_ID, AD_OrgTrx_ID, C_AcctSchema_ID, "; select = select + dateClause; select = select + " ,Account_ID, PostingType, " + accum.GetFACT_ACCUMULATION_ID(); if (accum.IsPRODUCT()) { select = select + " ,M_Product_ID "; } else { select = select + " , NULL "; } if (accum.IsBUSINESSPARTNER()) { select = select + " , C_BPartner_ID "; } else { select = select + " ,NULL "; } if (accum.IsPROJECT()) { select = select + " ,C_Project_ID "; } else { select = select + " , NULL "; } if (accum.IsSALESREGION()) { select = select + ", C_SalesRegion_ID "; } else { select = select + " ,NULL "; } if (accum.IsACTIVITY()) { select = select + " ,C_Activity_ID "; } else { select = select + " ,NULL "; } if (accum.IsCAMPAIGN()) { select = select + " ,C_Campaign_ID "; } else { select = select + " ,NULL "; } if (accum.IsLOCATIONTO()) { select = select + " ,C_LocTo_ID "; } else { select = select + " ,NULL "; } if (accum.IsLOCATIONFROM()) { select = select + " ,C_LocFrom_ID "; } else { select = select + " ,NULL "; } if (accum.IsUSERLIST1()) { select = select + " ,User1_ID "; } else { select = select + " ,NULL "; } if (accum.IsUSERLIST2()) { select = select + " ,User2_ID "; } else { select = select + " ,NULL "; } if (accum.IsBUDGET()) { select = select + " ,GL_Budget_ID "; } else { select = select + " ,NULL "; } if (accum.IsUSERELEMENT1()) { select = select + " ,UserElement1_ID "; } else { select = select + " ,NULL "; } if (accum.IsUSERELEMENT2()) { select = select + " , UserElement2_ID "; } else { select = select + " ,NULL "; } select = select + " ,COALESCE(SUM(AmtAcctDr),0), COALESCE(SUM(AmtAcctCr),0), COALESCE(SUM(Qty),0) "; String from = " FROM Fact_Acct a "; if (X_Fact_Accumulation.BALANCEACCUMULATION_PeriodOfAViennaCalendar.Equals(type)) { from += " ,(SELECT StartDate,EndDate FROM C_Period " + " WHERE C_Year_ID IN (SELECT C_Year_ID " + " FROM C_Year WHERE C_Calendar_ID= " + accum.GetC_Calendar_ID() + " ) " + " AND IsActive='Y' AND PeriodType='S') Period"; //param.add(new Integer(accum.GetC_Calendar_ID())); } String where = " WHERE C_AcctSchema_ID= " + C_AcctSchema_ID; if (X_Fact_Accumulation.BALANCEACCUMULATION_PeriodOfAViennaCalendar.Equals(type)) { where += " AND a.DateAcct BETWEEN TRUNC(Period.StartDate,'DD') AND TRUNC(Period.EndDate,'DD') "; } //param.add(new Integer(C_AcctSchema_ID)); if (dateFrom != null) { //SimpleDateFormat df = new SimpleDateFormat();//"dd-MM-yyyy" //String finalDate = df.Format(dateFrom); //where += " AND DateAcct>= TO_DATE('" + finalDate + "','DD-MM-YYYY') "; where += " AND DateAcct >= " + DB.TO_DATE(dateFrom); } String groupBy = " GROUP BY AD_Client_ID,C_AcctSchema_ID, AD_Org_ID, " + " AD_OrgTrx_ID,Account_ID, PostingType," + dateClause; if (accum.IsPRODUCT()) { groupBy = groupBy + " ,M_Product_ID "; } if (accum.IsBUSINESSPARTNER()) { groupBy = groupBy + " ,C_BPartner_ID "; } if (accum.IsPROJECT()) { groupBy = groupBy + " ,C_Project_ID "; } if (accum.IsSALESREGION()) { groupBy = groupBy + " ,C_SalesRegion_ID "; } if (accum.IsACTIVITY()) { groupBy = groupBy + ", C_Activity_ID "; } if (accum.IsCAMPAIGN()) { groupBy = groupBy + " ,C_Campaign_ID "; } if (accum.IsLOCATIONTO()) { groupBy = groupBy + ", C_LocTo_ID "; } if (accum.IsLOCATIONFROM()) { groupBy = groupBy + ", C_LocFrom_ID "; } if (accum.IsUSERLIST1()) { groupBy = groupBy + ", User1_ID"; } if (accum.IsUSERLIST2()) { groupBy = groupBy + ", User2_ID "; } if (accum.IsBUDGET()) { groupBy = groupBy + ", GL_Budget_ID "; } if (accum.IsUSERELEMENT1()) { groupBy = groupBy + ", UserElement1_ID "; } if (accum.IsUSERELEMENT2()) { groupBy = groupBy + ", UserElement2_ID "; } String sql = insert + select + from + where + groupBy; no = DB.ExecuteQuery(sql, null, trx); _log.Config("Inserts=" + no); if (svrPrc != null) { svrPrc.AddLog(0, dateFrom, new Decimal(no), "Inserts in " + accum.ToString()); } trx.Commit(); } start = CommonFunctions.CurrentTimeMillis() - start; _log.Info((start / 1000) + " sec"); return("#" + no); }
/** * Delete Balances * @param AD_Client_ID client * @param C_AcctSchema_ID accounting schema 0 for all * @param dateFrom null for all or first date to delete * @param trx transaction * @param svrPrc optional server process * @return Message to be translated */ public static String DeleteBalance(int AD_Client_ID, int C_AcctSchema_ID, DateTime?dateFrom, Trx trxp, int Fact_Accumulation_ID, SvrProcess svrPrc) { Trx trx = trxp; //List<Object> param = new List<Object>(); StringBuilder sql = new StringBuilder("DELETE FROM Fact_Acct_Balance WHERE AD_Client_ID=" + AD_Client_ID); //param.add(new Integer(AD_Client_ID)); if (C_AcctSchema_ID != 0) { sql.Append(" AND C_AcctSchema_ID=" + C_AcctSchema_ID); // param.add(new Integer(C_AcctSchema_ID)); } if (dateFrom != null) { //SimpleDateFormat df = new SimpleDateFormat(); //String finalDate = df.Format(dateFrom); //sql.Append(" AND DateAcct>= TO_DATE('" + finalDate + "','DD-MM-YYYY')"); sql.Append(" AND DateAcct >= " + DB.TO_DATE(dateFrom)); } if (Fact_Accumulation_ID != 0) { sql.Append(" AND Fact_Accumulation_ID = " + Fact_Accumulation_ID); //param.add(new Integer(Fact_Accumulation_ID)); } // int no = DB.ExecuteQuery(sql.ToString(), null, trx); String msg = "@Deleted@=" + no; _log.Info("C_AcctSchema_ID=" + C_AcctSchema_ID + ",DateAcct=" + dateFrom + " #=" + no); if (svrPrc != null) { svrPrc.AddLog(0, dateFrom, new Decimal(no), "Deleted"); } trx.Commit(); // return(msg); }