Esempio n. 1
0
        /**
         *  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);
        }