Beispiel #1
0
 /// <summary>
 /// After Save
 /// </summary>
 /// <param name="newRecord"></param>
 /// <param name="success"></param>
 /// <returns>Success</returns>
 protected override bool AfterSave(bool newRecord, bool success)
 {
     if (newRecord)
     {
         MRevenueRecognition rr = new MRevenueRecognition(GetCtx(), GetC_RevenueRecognition_ID(), Get_TrxName());
         if (rr.IsTimeBased())
         {
             /**	Get InvoiveQty
              * SELECT	QtyInvoiced, M_Product_ID
              * INTO	v_Qty, v_M_Product_ID
              * FROM	C_InvoiceLine
              * WHERE    C_InvoiceLine_ID=:new.C_InvoiceLine_ID;
              * --	Insert
              * AD_Sequence_Next ('C_ServiceLevel', :new.AD_Client_ID, v_NextNo);
              * INSERT INTO C_ServiceLevel
              *  (C_ServiceLevel_ID, C_RevenueRecognition_Plan_ID,
              *  AD_Client_ID,AD_Org_ID,IsActive,Created,CreatedBy,Updated,UpdatedBy,
              *  M_Product_ID, Description, ServiceLevelInvoiced, ServiceLevelProvided,
              *  Processing,Processed)
              * VALUES
              *  (v_NextNo, :new.C_RevenueRecognition_Plan_ID,
              *  :new.AD_Client_ID,:new.AD_Org_ID,'Y',SysDate,:new.CreatedBy,SysDate,:new.UpdatedBy,
              *  v_M_Product_ID, NULL, v_Qty, 0,
              *  'N', 'N');
              **/
         }
     }
     return(success);
 }       //	afterSave
        /// <summary>
        /// This function is used to get data of MRevenueRecognitionRun
        /// </summary>
        /// <param name="revenueRecognition">Revenue Recognition reference</param>
        /// <param name="recognitionDate">Recognition Date</param>
        /// <param name="_orgId">Org ID</param>
        /// <param name="reverse">is reverse or not</param>
        /// <returns>array of MRevenueRecognitionRun</returns>
        public static MRevenueRecognitionRun[] GetRecognitionRuns(MRevenueRecognition revenueRecognition, DateTime?recognitionDate, int _orgId, bool reverse)
        {
            List <MRevenueRecognitionRun> list = new List <MRevenueRecognitionRun>();
            string sql = "Select  rn.* from C_RevenueRecognition_Run rn " +
                         " JOIN c_revenuerecognition_plan pl ON pl.c_revenuerecognition_plan_id = rn.c_revenuerecognition_plan_id " +
                         " WHERE pl.c_revenuerecognition_id =" + revenueRecognition.GetC_RevenueRecognition_ID();

            if (!reverse)
            {
                sql += " And RecognitionDate <=" + GlobalVariable.TO_DATE(recognitionDate, true);
            }
            if (_orgId > 0)
            {
                sql += " AND pl.AD_Org_ID=" + _orgId;
            }
            sql += " And NVL(GL_Journal_ID,0) <= 0 ORDER BY rn.RecognitionDate, pl.c_currency_id, pl.c_acctschema_id";

            DataTable   dt  = null;
            IDataReader idr = null;

            try
            {
                idr = DB.ExecuteReader(sql, null, revenueRecognition.Get_Trx());
                dt  = new DataTable();
                dt.Load(idr);
                idr.Close();
                foreach (DataRow dr in dt.Rows)
                {
                    list.Add(new MRevenueRecognitionRun(revenueRecognition.GetCtx(), dr, revenueRecognition.Get_Trx()));
                }
            }
            catch (Exception e)
            {
                if (idr != null)
                {
                    idr.Close();
                }
                _log.Log(Level.SEVERE, sql, e);
            }
            finally
            {
                dt = null;
            }

            MRevenueRecognitionRun[] retValue = new MRevenueRecognitionRun[list.Count];
            retValue = list.ToArray();
            return(retValue);
        }
        /// <summary>
        /// This Function is used to gete Plan data record based on respective parameters
        /// </summary>
        /// <param name="RevenueRecognition">Revenue Recognition Reference</param>
        /// <param name="InvoiceLine_ID">Invoice Line Reference</param>
        /// <param name="OrgId">Org ID</param>
        /// <returns>Array of MRevenueRecognitionPlan</returns>
        public static MRevenueRecognitionPlan[] GetRecognitionPlans(MRevenueRecognition RevenueRecognition, int InvoiceLine_ID, int OrgId)
        {
            List <MRevenueRecognitionPlan> list = new List <MRevenueRecognitionPlan>();
            string sql = "SELECT * FROM C_RevenueRecognition_Plan pl";

            if (InvoiceLine_ID > 0)
            {
                sql += @" INNER  JOIN c_invoiceline invl ON invl.c_invoiceline_id = pl.c_invoiceline_id 
                            WHERE pl.C_RevenueRecognition_ID=" + RevenueRecognition.GetC_RevenueRecognition_ID() +
                       " AND invl.c_invoiceLine_id=" + InvoiceLine_ID + " AND invl.ad_org_id=" + OrgId;
            }
            else
            {
                sql += " WHERE pl.C_RevenueRecognition_ID=" + RevenueRecognition.GetC_RevenueRecognition_ID() + " AND pl.ad_org_id=" + OrgId;
            }
            DataTable   dt  = null;
            IDataReader idr = null;

            try
            {
                idr = DB.ExecuteReader(sql, null, RevenueRecognition.Get_Trx());
                dt  = new DataTable();
                dt.Load(idr);
                idr.Close();
                foreach (DataRow dr in dt.Rows)
                {
                    list.Add(new MRevenueRecognitionPlan(RevenueRecognition.GetCtx(), dr, RevenueRecognition.Get_Trx()));
                }
            }
            catch (Exception e)
            {
                if (idr != null)
                {
                    idr.Close();
                }
                _log.Log(Level.SEVERE, sql, e);
            }
            finally
            {
                dt = null;
            }

            MRevenueRecognitionPlan[] retValue = new MRevenueRecognitionPlan[list.Count];
            retValue = list.ToArray();
            return(retValue);
        }
        /// <summary>
        /// This Function is used to get RevenueRecognition Records
        /// </summary>
        /// <param name="ctx">ctx</param>
        /// <param name="trx">trx</param>
        /// <returns>Array of MRevenueRecognition</returns>
        public static MRevenueRecognition[] GetRecognitions(Ctx ctx, Trx trx)
        {
            List <MRevenueRecognition> list = new List <MRevenueRecognition>();
            string sql = "Select * From C_RevenueRecognition Where AD_Client_ID=" + ctx.GetAD_Client_ID();

            DataTable   dt  = null;
            IDataReader idr = null;

            try
            {
                idr = DB.ExecuteReader(sql, null, null);
                dt  = new DataTable();
                dt.Load(idr);
                idr.Close();
                foreach (DataRow dr in dt.Rows)
                {
                    list.Add(new MRevenueRecognition(ctx, dr, trx));
                }
            }
            catch (Exception e)
            {
                if (idr != null)
                {
                    idr.Close();
                }
                _log.Log(Level.SEVERE, sql, e);
            }
            finally
            {
                dt = null;
            }

            MRevenueRecognition[] retValue = new MRevenueRecognition[list.Count];
            retValue = list.ToArray();
            return(retValue);
        }
        /// <summary>
        /// This function is used to create Recognition plan and run
        /// </summary>
        /// <param name="C_InvoiceLine_ID">invoice line</param>
        /// <param name="C_RevenueRecognition_ID">Revenue Recognition</param>
        /// <param name="Invoice">Invoice</param>
        /// <returns>true, when success</returns>
        public static bool CreateRevenueRecognitionPlan(int C_InvoiceLine_ID, int C_RevenueRecognition_ID, MInvoice Invoice)
        {
            try
            {
                MRevenueRecognitionRun revenueRecognitionRun = null;
                DateTime?           RecognizationDate        = null;
                int                 NoofMonths             = 0;
                MRevenueRecognition revenueRecognition     = new MRevenueRecognition(Invoice.GetCtx(), C_RevenueRecognition_ID, Invoice.Get_Trx());
                int                 defaultAccSchemaOrg_ID = GetDefaultActSchema(Invoice.GetCtx(), Invoice.GetAD_Client_ID(), Invoice.GetAD_Org_ID());
                int                 ToCurrency             = Util.GetValueOfInt(DB.ExecuteScalar("SELECT C_Currency_ID FROM C_AcctSchema WHERE C_AcctSchema_ID=" + defaultAccSchemaOrg_ID));

                MInvoiceLine invoiceLine = new MInvoiceLine(Invoice.GetCtx(), C_InvoiceLine_ID, Invoice.Get_Trx());

                //if recoganization date is null recognition plan and run cant be generated
                if (invoiceLine.Get_Value("RevenueStartDate") == null)
                {
                    _log.Log(Level.SEVERE, "DateIsInvlidOrNull");
                    return(false);
                }
                RecognizationDate = Util.GetValueOfDateTime(invoiceLine.Get_Value("RevenueStartDate"));

                // precision to be handle based on std precision defined on acct schema
                string sql          = "SELECT C.StdPrecision FROM C_AcctSchema a INNER JOIN C_Currency c ON c.C_Currency_ID= a.C_Currency_ID WHERE a.C_AcctSchema_ID=" + defaultAccSchemaOrg_ID;
                int    stdPrecision = Util.GetValueOfInt(DB.ExecuteScalar(sql, null, null));


                if (revenueRecognition.GetRecognitionFrequency().Equals(RECOGNITIONFREQUENCY_Month) && RecognizationDate.Value.Day != 1 && revenueRecognition.GetNoMonths() > 0)
                {
                    //if startdate is in between day of month
                    NoofMonths = revenueRecognition.GetNoMonths() + 1;
                }
                else
                {
                    //if start date is the first day of the month
                    NoofMonths = revenueRecognition.GetNoMonths();
                }
                MRevenueRecognitionPlan revenueRecognitionPlan = new MRevenueRecognitionPlan(Invoice.GetCtx(), 0, Invoice.Get_Trx());
                revenueRecognitionPlan.SetRecognitionPlan(invoiceLine, Invoice, C_RevenueRecognition_ID, ToCurrency);
                revenueRecognitionPlan.SetC_AcctSchema_ID(defaultAccSchemaOrg_ID);
                revenueRecognitionPlan.SetRecognizedAmt(0);
                if (!revenueRecognitionPlan.Save())
                {
                    ValueNamePair pp    = VLogger.RetrieveError();
                    string        error = pp != null?pp.GetValue() : "";

                    if (pp != null && string.IsNullOrEmpty(error))
                    {
                        error = pp.GetName();
                    }
                    if (!string.IsNullOrEmpty(error))
                    {
                        _log.Log(Level.SEVERE, error);
                        return(false);
                    }
                }
                else
                {
                    if (!revenueRecognition.IsTimeBased())
                    {
                    }
                    else
                    {
                        if (revenueRecognition.GetRecognitionFrequency().Equals(RECOGNITIONFREQUENCY_Month))
                        {
                            decimal totaldays = Util.GetValueOfDecimal((RecognizationDate.Value.AddMonths(revenueRecognition.GetNoMonths()) - RecognizationDate.Value.Date).TotalDays);

                            decimal  perdayAmt     = Math.Round(revenueRecognitionPlan.GetTotalAmt() / (totaldays > 0?totaldays:1), 12);
                            decimal  recognizedAmt = 0;
                            DateTime?lastdate      = null;
                            int      days          = 0;
                            for (int i = 0; i < NoofMonths; i++)
                            {
                                if (i == 0)
                                {
                                    if (RecognizationDate.Value.Month == 12)
                                    {
                                        //last date of the month
                                        lastdate = new DateTime(RecognizationDate.Value.Year, RecognizationDate.Value.Month, 1).AddMonths(1).AddDays(-1);
                                    }
                                    else
                                    {
                                        //last date of the month
                                        lastdate = new DateTime(RecognizationDate.Value.Year, RecognizationDate.Value.Month + 1, 1).AddDays(-1);
                                    }
                                    days  = Util.GetValueOfInt((lastdate.Value.Date - RecognizationDate.Value.Date).TotalDays);
                                    days += 1;
                                }
                                else if (i == (revenueRecognition.GetNoMonths()))
                                {
                                    //last date of the month would the day before  the recoganizationdate
                                    lastdate = RecognizationDate.Value.AddMonths(i).AddDays(-1);
                                    DateTime startDate = new DateTime(lastdate.Value.Year, lastdate.Value.Month, 1);
                                    days  = Util.GetValueOfInt((lastdate.Value.Date - startDate.Date).TotalDays);
                                    days += 1;
                                }
                                else
                                {
                                    DateTime startDate = lastdate.Value.AddDays(1);
                                    days     = DateTime.DaysInMonth(startDate.Year, startDate.Month);
                                    lastdate = startDate.AddDays(days - 1);
                                }
                                recognizedAmt         = Math.Round(days * perdayAmt, stdPrecision);
                                revenueRecognitionRun = new MRevenueRecognitionRun(Invoice.GetCtx(), 0, Invoice.Get_Trx());
                                revenueRecognitionRun.SetRecognitionRun(revenueRecognitionPlan);
                                revenueRecognitionRun.SetRecognizedAmt(recognizedAmt);
                                revenueRecognitionRun.SetRecognitionDate(lastdate);
                                if (!revenueRecognitionRun.Save())
                                {
                                    ValueNamePair pp    = VLogger.RetrieveError();
                                    string        error = pp != null?pp.GetValue() : "";

                                    if (pp != null && string.IsNullOrEmpty(error))
                                    {
                                        error = pp.GetName();
                                    }
                                    if (!string.IsNullOrEmpty(error))
                                    {
                                        _log.Log(Level.SEVERE, error);
                                        return(false);
                                    }
                                }
                                recognizedAmt = 0;
                            }
                        }
                        else if (revenueRecognition.GetRecognitionFrequency().Equals(RECOGNITIONFREQUENCY_Day))
                        {
                            Decimal recognizedAmt = Math.Round(revenueRecognitionPlan.GetTotalAmt() / revenueRecognition.GetNoMonths(), stdPrecision);
                            int     days          = 0;
                            for (int i = 0; i < revenueRecognition.GetNoMonths(); i++)
                            {
                                revenueRecognitionRun = new MRevenueRecognitionRun(Invoice.GetCtx(), 0, Invoice.Get_Trx());
                                revenueRecognitionRun.SetRecognitionRun(revenueRecognitionPlan);
                                revenueRecognitionRun.SetRecognizedAmt(recognizedAmt);
                                revenueRecognitionRun.SetRecognitionDate(RecognizationDate.Value.AddDays(days));
                                days += 1;
                                if (!revenueRecognitionRun.Save())
                                {
                                    ValueNamePair pp    = VLogger.RetrieveError();
                                    string        error = pp != null?pp.GetValue() : "";

                                    if (pp != null && string.IsNullOrEmpty(error))
                                    {
                                        error = pp.GetName();
                                    }
                                    if (!string.IsNullOrEmpty(error))
                                    {
                                        _log.Log(Level.SEVERE, error);
                                        return(false);
                                    }
                                }
                            }
                        }
                        else if (revenueRecognition.GetRecognitionFrequency().Equals(RECOGNITIONFREQUENCY_Year))
                        {
                            DateTime?fstartDate  = null;
                            DateTime?fendDate    = null;
                            int      calendar_ID = 0;
                            DataSet  ds          = new DataSet();

                            calendar_ID = Util.GetValueOfInt(DB.ExecuteScalar("SELECT C_Calendar_ID FROM AD_OrgInfo WHERE ad_org_id = " + Invoice.GetAD_Org_ID()));
                            if (calendar_ID == 0)
                            {
                                calendar_ID = Util.GetValueOfInt(DB.ExecuteScalar("SELECT C_Calendar_ID FROM AD_ClientInfo WHERE ad_client_id = " + Invoice.GetAD_Client_ID()));
                            }
                            sql = "SELECT startdate , enddate FROM c_period WHERE " +
                                  "c_year_id = (SELECT c_year.c_year_id FROM c_year INNER JOIN C_period ON c_year.c_year_id = C_period.c_year_id " +
                                  "WHERE  c_year.c_calendar_id =" + calendar_ID + " AND " + GlobalVariable.TO_DATE(RecognizationDate, true) + " BETWEEN C_period.startdate AND C_period.enddate) AND periodno IN (1, 12)";
                            ds = DB.ExecuteDataset(sql);
                            if (ds != null && ds.Tables[0].Rows.Count > 0)
                            {
                                fstartDate = Convert.ToDateTime(ds.Tables[0].Rows[0]["startdate"]);
                                fendDate   = Convert.ToDateTime(ds.Tables[0].Rows[1]["enddate"]);
                            }
                            if (fstartDate != RecognizationDate)
                            {
                                //RecognizationDate  is not same as financial year's start date
                                NoofMonths += 1;
                            }
                            decimal totaldays = Util.GetValueOfDecimal((RecognizationDate.Value.AddYears(revenueRecognition.GetNoMonths()) - RecognizationDate.Value.Date).TotalDays);

                            decimal  perdayAmt     = Math.Round(revenueRecognitionPlan.GetTotalAmt() / (totaldays > 0?totaldays:1), 12);
                            decimal  recognizedAmt = 0;
                            DateTime?lastdate      = null;
                            int      days          = 0;
                            for (int i = 0; i < NoofMonths; i++)
                            {
                                if (i == 0)
                                {
                                    //last date will always be financial year's end date
                                    lastdate = fendDate;
                                    days     = Util.GetValueOfInt((lastdate.Value.Date - RecognizationDate.Value.Date).TotalDays);
                                    days    += 1;
                                }
                                else if (i == revenueRecognition.GetNoMonths())
                                {
                                    //last date of the year would the day before the recoganizationdate
                                    lastdate = RecognizationDate.Value.AddYears(i).AddDays(-1);
                                    DateTime startDate = fstartDate.Value.AddYears(i);
                                    days  = Util.GetValueOfInt((lastdate.Value.Date - startDate.Date).TotalDays);
                                    days += 1;
                                }
                                else
                                {
                                    lastdate = fendDate.Value.AddYears(i);
                                    DateTime _startDate = fstartDate.Value.AddYears(i);
                                    days  = Util.GetValueOfInt((lastdate.Value.Date - _startDate.Date).TotalDays);
                                    days += 1;
                                }
                                recognizedAmt         = Math.Round(days * perdayAmt, stdPrecision);
                                revenueRecognitionRun = new MRevenueRecognitionRun(Invoice.GetCtx(), 0, Invoice.Get_Trx());
                                revenueRecognitionRun.SetRecognitionRun(revenueRecognitionPlan);
                                revenueRecognitionRun.SetRecognizedAmt(recognizedAmt);
                                revenueRecognitionRun.SetRecognitionDate(lastdate);
                                if (!revenueRecognitionRun.Save())
                                {
                                    ValueNamePair pp    = VLogger.RetrieveError();
                                    string        error = pp != null?pp.GetValue() : "";

                                    if (pp != null && string.IsNullOrEmpty(error))
                                    {
                                        error = pp.GetName();
                                    }
                                    if (!string.IsNullOrEmpty(error))
                                    {
                                        _log.Log(Level.SEVERE, error);
                                        return(false);
                                    }
                                }
                                recognizedAmt = 0;
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                _log.Severe("Exception during creation of Recognition Plan and Run. " + ex.Message);
                return(false);
            }
            return(true);
        }