/// <summary>
        /// Add Invoices to Run tab of dunning run window.
        /// </summary>
        /// <returns>No of invoices that are created.</returns>
        private int AddInvoices()
        {
            //int size = 3;
            int count = 0;

            SqlParameter[] param = null;
            // Changes for Invoice schedule if valid true

            if (!_level.IsShowNotDue())
            {
                param = new SqlParameter[3];
            }
            else
            {
                param = new SqlParameter[1];
            }

            sql.Append("SELECT I.C_INVOICE_ID,  I.C_CURRENCY_ID, I.GRANDTOTAL, INVOICEOPEN(I.C_INVOICE_ID,Ips.C_INVOICEPAYSCHEDULE_ID),  "
                       + " DAYSBETWEEN(@param1,IPS.DUEDATE),"
                       + " i.IsInDispute, i.C_BPartner_ID, "
                       + " CASE "
                       + " WHEN i.issotrx   ='Y' AND i.isreturntrx='N'" // Invoice Customer
                       + " THEN ips.DueAmt"
                       + " WHEN i.issotrx   ='Y' AND i.isreturntrx='Y'" // AR Credit Memo
                       + " THEN ips.DueAmt * -1"
                       + " WHEN i.issotrx   ='N' AND i.isreturntrx='N'" // Invoice Vendor
                       + " THEN ips.DueAmt * -1"
                       + " ELSE ips.DueAmt"                             // AP Credit Memo
                       + " END AS DueAmt,"
                       + " ips.C_INVOICEPAYSCHEDULE_ID"
                       + " FROM C_Invoice i "
                       + " LEFT JOIN C_INVOICEPAYSCHEDULE ips "
                       + " ON ips.c_invoice_id               =i.c_invoice_id "
                       + " WHERE ips.VA009_IsPaid                    ='N' "
                       + " AND i.AD_Client_ID                = " + _run.GetAD_Client_ID()
                       + " AND i.AD_Org_ID                   = " + _run.GetAD_Org_ID()
                       + " AND i.DocStatus                  IN ('CO','CL') "
                       + " AND (NOT i.InvoiceCollectionType IN ('" + X_C_Invoice.INVOICECOLLECTIONTYPE_CollectionAgency + "', "
                       + "'" + X_C_Invoice.INVOICECOLLECTIONTYPE_LegalProcedure + "', '" + X_C_Invoice.INVOICECOLLECTIONTYPE_Uncollectable + "')"
                       + " OR InvoiceCollectionType IS NULL) ");
            if (_SalesRep_ID > 0)
            {
                sql.Append(" AND i.SalesRep_ID= " + _SalesRep_ID);
            }
            if (!_level.IsShowNotDue())
            {
                sql.Append(" AND IPS.DUEDATE                  <=@param2 "
                           + " AND DATEINVOICED                 <=@param3 ");
            }
            sql.Append(" AND IPS.ISVALID='Y' "
                       + " AND i.ispayschedulevalid='Y' "
                       + " AND EXISTS "
                       + " (SELECT * "
                       + " FROM C_DunningLevel dl "
                       + " WHERE dl.C_DunningLevel_ID= " + _run.GetC_DunningLevel_ID()
                       + " AND dl.C_Dunning_ID      IN "
                       + " (SELECT COALESCE(bp.C_Dunning_ID, bpg.C_Dunning_ID) "
                       + " FROM C_BPartner bp "
                       + " INNER JOIN C_BP_Group bpg "
                       + " ON (bp.C_BP_Group_ID =bpg.C_BP_Group_ID) "
                       + " WHERE i.C_BPartner_ID=bp.C_BPartner_ID "
                       + " ) "
                       + " )");


            if (!_level.IsShowAllDue())
            {
                param[0] = new SqlParameter("@param1", _run.GetDunningDate());
                if (!_level.IsShowNotDue())
                {
                    param[1] = new SqlParameter("@param2", _run.GetDunningDate().Value.AddDays(Util.GetValueOfInt(-1 * _level.GetDaysAfterDue())));
                    param[2] = new SqlParameter("@param3", _run.GetDunningDate().Value.AddDays(Util.GetValueOfInt(-1 * _level.GetDaysAfterDue())));
                }
            }
            else
            {
                param[0] = new SqlParameter("@param1", _run.GetDunningDate());
                if (!_level.IsShowNotDue())
                {
                    param[1] = new SqlParameter("@param2", _run.GetDunningDate());
                    param[2] = new SqlParameter("@param3", _run.GetDunningDate());
                }
            }

            // for specific Business Partner
            if (_C_BPartner_ID != 0)
            {
                //size = size + 1;
                sql.Append(" AND i.C_BPartner_ID=" + _C_BPartner_ID);   //	##6
            }
            // or a specific group
            if (_C_BP_Group_ID != 0)
            {
                //size = size + 1;
                sql.Append(" AND EXISTS (SELECT * FROM C_BPartner bp "
                           + "WHERE i.C_BPartner_ID=bp.C_BPartner_ID AND bp.C_BP_Group_ID=" + _C_BP_Group_ID + ")"); //	##;
            }
            // Only Sales Trx
            if (_OnlySOTrx)
            {
                sql.Append(" AND i.IsSOTrx='Y'");
            }
            // Only single currency
            if (!_IsAllCurrencies)
            {
                sql.Append(" AND i.C_Currency_ID=" + _C_Currency_ID);
            }
            //	log.info(sql);

            String sql2 = null;

            // if sequentially we must check for other levels with smaller days for
            // which this invoice is not yet included!
            if (_level.GetParent().IsCreateLevelsSequentially())
            {
                // Build a list of all topmost Dunning Levels
                MDunningLevel[] previousLevels = _level.GetPreviousLevels();
                if (previousLevels != null && previousLevels.Length > 0)
                {
                    for (int i = 0; i < previousLevels.Length; i++)
                    {
                        sql.Append(" AND i.C_Invoice_ID IN (SELECT C_Invoice_ID FROM C_DunningRunLine WHERE " +
                                   "C_DunningRunEntry_ID IN (SELECT C_DunningRunEntry_ID FROM C_DunningRunEntry WHERE " +
                                   "C_DunningRun_ID IN (SELECT C_DunningRun_ID FROM C_DunningRun WHERE " +
                                   "C_DunningLevel_ID=" + previousLevels[i].Get_ID() + ")) AND Processed<>'N')");
                    }
                }
            }
            // ensure that we do only dunn what's not yet dunned, so we lookup the max of last Dunn Date which was processed
            sql2 = "SELECT COUNT(*), COALESCE(DAYSBETWEEN(MAX(dr2.DunningDate), MAX(dr.DunningDate)),0)"
                   + "FROM C_DunningRun dr2, C_DunningRun dr"
                   + " INNER JOIN C_DunningRunEntry dre ON (dr.C_DunningRun_ID=dre.C_DunningRun_ID)"
                   + " INNER JOIN C_DunningRunLine drl ON (dre.C_DunningRunEntry_ID=drl.C_DunningRunEntry_ID) "
                   + "WHERE drl.Processed='Y' AND dr2.C_DunningRun_ID=@C_DunningRun_ID AND drl.C_Invoice_ID=@C_Invoice_ID AND C_InvoicePaySchedule_ID=@C_InvoicePaySchedule_ID AND dr.C_DunningRun_ID!=@C_DunningRun_ID"; // ##1 ##2

            Decimal DaysAfterDue       = _run.GetLevel().GetDaysAfterDue();
            int     DaysBetweenDunning = _run.GetLevel().GetDaysBetweenDunning();

            IDataReader idr  = null;
            IDataReader idr1 = null;

            try
            {
                idr = DataBase.DB.ExecuteReader(sql.ToString(), param, Get_TrxName());
                sql.Clear();
                //
                while (idr.Read())
                {
                    C_Invoice_ID = 0; C_Currency_ID = 0; GrandTotal = 0; Open = 0; DaysDue = 0;
                    IsInDispute  = false; C_BPartner_ID = 0; PaySchedule_ID = 0;

                    C_Invoice_ID   = Utility.Util.GetValueOfInt(idr[0]);
                    C_Currency_ID  = Utility.Util.GetValueOfInt(idr[1]);
                    GrandTotal     = Utility.Util.GetValueOfDecimal(idr[2]);
                    Open           = Utility.Util.GetValueOfDecimal(idr[7]);
                    DaysDue        = Utility.Util.GetValueOfInt(idr[4]);
                    IsInDispute    = "Y".Equals(Utility.Util.GetValueOfString(idr[5]));
                    C_BPartner_ID  = Utility.Util.GetValueOfInt(idr[6]);
                    PaySchedule_ID = Utility.Util.GetValueOfInt(idr[8]);

                    //
                    // Check for Dispute
                    if (!_IncludeInDispute && IsInDispute)
                    {
                        continue;
                    }

                    // Check for an open amount
                    if (Env.ZERO.CompareTo(Open) == 0)
                    {
                        continue;
                    }
                    //
                    timesDunned   = 0;
                    daysAfterLast = 0;

                    //2nd record set
                    SqlParameter[] param1 = new SqlParameter[3];
                    param1[0] = new SqlParameter("@C_DunningRun_ID", _run.Get_ID());
                    param1[1] = new SqlParameter("@C_Invoice_ID", C_Invoice_ID);
                    param1[2] = new SqlParameter("@C_InvoicePaySchedule_ID", PaySchedule_ID);
                    idr1      = DataBase.DB.ExecuteReader(sql2, param1, Get_TrxName());
                    //	SubQuery
                    if (idr1.Read())
                    {
                        timesDunned   = Utility.Util.GetValueOfInt(idr1[0]);
                        daysAfterLast = Utility.Util.GetValueOfInt(idr1[1]);
                    }
                    idr1.Close();
                    //	SubQuery

                    // Ensure that Daysbetween Dunning is enforced
                    // Ensure Not ShowAllDue and Not ShowNotDue is selected
                    // PROBLEM: If you have ShowAll activated then DaysBetweenDunning is not working, because we don't know whether
                    //          there is something which we really must Dunn.
                    //if (DaysBetweenDunning != 0 && daysAfterLast != 0 && daysAfterLast < DaysBetweenDunning && !_level.IsShowAllDue() && !_level.IsShowNotDue())
                    if (DaysBetweenDunning != 0 && daysAfterLast != 0 && daysAfterLast < DaysBetweenDunning)
                    {
                        continue;
                    }


                    // We will minus the timesDunned if this is the DaysBetweenDunning is not fullfilled.
                    // Remember in checkup later we must reset them!
                    // See also checkDunningEntry()
                    if (daysAfterLast < DaysBetweenDunning)
                    {
                        timesDunned = timesDunned * -1;
                    }
                    //
                    if (CreateInvoiceLine(C_Invoice_ID, C_Currency_ID, GrandTotal, Open,
                                          DaysDue, IsInDispute, C_BPartner_ID,
                                          timesDunned, daysAfterLast, PaySchedule_ID))
                    {
                        count++;
                    }
                }
                idr.Close();
            }
            catch (Exception e)
            {
                if (idr != null)
                {
                    idr.Close();
                }
                if (idr1 != null)
                {
                    idr1.Close();
                }
                log.Log(Level.SEVERE, "addInvoices", e);
            }

            return(count);
        }
Пример #2
0
        /// <summary>
        /// Add Invoices to Run
        /// </summary>
        /// <returns>no of invoices</returns>
        private int AddInvoices()
        {
            int    size  = 5;
            int    count = 0;
            String sql   = "SELECT i.C_Invoice_ID, i.C_Currency_ID,"
                           + " i.GrandTotal*i.MultiplierAP,"
                           + " invoiceOpen(i.C_Invoice_ID,i.C_InvoicePaySchedule_ID)*MultiplierAP,"
                           + " COALESCE(daysBetween(@param1,ips.DueDate),paymentTermDueDays(i.C_PaymentTerm_ID,i.DateInvoiced,@param2))," // ##1/2
                           + " i.IsInDispute, i.C_BPartner_ID "
                           + "FROM C_Invoice_v i "
                           + " LEFT OUTER JOIN C_InvoicePaySchedule ips ON (i.C_InvoicePaySchedule_ID=ips.C_InvoicePaySchedule_ID) "
                           + "WHERE i.IsPaid='N' AND i.AD_Client_ID=@param3" //	##3
                           + " AND i.DocStatus IN ('CO','CL')"
                                                                             //  Invoice Collection Status Collection Agency, Uncollectable, Legal will not been dunned any longer as per Def. YS + KP 12/02/06
                           + " AND (NOT i.InvoiceCollectionType IN ('" + X_C_Invoice.INVOICECOLLECTIONTYPE_CollectionAgency + "', "
                           + "'" + X_C_Invoice.INVOICECOLLECTIONTYPE_LegalProcedure + "', '" + X_C_Invoice.INVOICECOLLECTIONTYPE_Uncollectable + "')"
                           + " OR InvoiceCollectionType IS NULL)"
                                                                  //  Do not show future docs...
                           + " AND DateInvoiced<=@param4"         // ##4
                                                                  //	Only BP(Group) with Dunning defined
                           + " AND EXISTS (SELECT * FROM C_DunningLevel dl "
                           + "WHERE dl.C_DunningLevel_ID=@param5" //	//	##5
                           + " AND dl.C_Dunning_ID IN "
                           + "(SELECT COALESCE(bp.C_Dunning_ID, bpg.C_Dunning_ID) "
                           + "FROM C_BPartner bp"
                           + " INNER JOIN C_BP_Group bpg ON (bp.C_BP_Group_ID=bpg.C_BP_Group_ID) "
                           + "WHERE i.C_BPartner_ID=bp.C_BPartner_ID))";

            // for specific Business Partner
            if (_C_BPartner_ID != 0)
            {
                size = size + 1;
                sql += " AND i.C_BPartner_ID=@param6";  //	##6
            }
            // or a specific group
            else if (_C_BP_Group_ID != 0)
            {
                size = size + 1;
                sql += " AND EXISTS (SELECT * FROM C_BPartner bp "
                       + "WHERE i.C_BPartner_ID=bp.C_BPartner_ID AND bp.C_BP_Group_ID=@param6)"; //	##6
            }
            // Only Sales Trx
            if (_OnlySOTrx)
            {
                sql += " AND i.IsSOTrx='Y'";
            }
            // Only single currency
            if (!_IsAllCurrencies)
            {
                sql += " AND i.C_Currency_ID=" + _C_Currency_ID;
            }
            //	log.info(sql);

            String sql2 = null;

            // if sequentially we must check for other levels with smaller days for
            // which this invoice is not yet included!
            if (_level.GetParent().IsCreateLevelsSequentially())
            {
                // Build a list of all topmost Dunning Levels
                MDunningLevel[] previousLevels = _level.GetPreviousLevels();
                if (previousLevels != null && previousLevels.Length > 0)
                {
                    String sqlAppend = "";
                    for (int i = 0; i < previousLevels.Length; i++)
                    {
                        sqlAppend += " AND i.C_Invoice_ID IN (SELECT C_Invoice_ID FROM C_DunningRunLine WHERE " +
                                     "C_DunningRunEntry_ID IN (SELECT C_DunningRunEntry_ID FROM C_DunningRunEntry WHERE " +
                                     "C_DunningRun_ID IN (SELECT C_DunningRun_ID FROM C_DunningRun WHERE " +
                                     "C_DunningLevel_ID=" + previousLevels[i].Get_ID() + ")) AND Processed<>'N')";
                    }
                    sql += sqlAppend;
                }
            }
            // ensure that we do only dunn what's not yet dunned, so we lookup the max of last Dunn Date which was processed
            sql2 = "SELECT COUNT(*), COALESCE(DAYSBETWEEN(MAX(dr2.DunningDate), MAX(dr.DunningDate)),0)"
                   + "FROM C_DunningRun dr2, C_DunningRun dr"
                   + " INNER JOIN C_DunningRunEntry dre ON (dr.C_DunningRun_ID=dre.C_DunningRun_ID)"
                   + " INNER JOIN C_DunningRunLine drl ON (dre.C_DunningRunEntry_ID=drl.C_DunningRunEntry_ID) "
                   + "WHERE drl.Processed='Y' AND dr2.C_DunningRun_ID=@C_DunningRun_ID AND drl.C_Invoice_ID=@C_Invoice_ID"; // ##1 ##2

            Decimal DaysAfterDue       = _run.GetLevel().GetDaysAfterDue();
            int     DaysBetweenDunning = _run.GetLevel().GetDaysBetweenDunning();

            IDataReader idr  = null;
            IDataReader idr1 = null;

            try
            {
                SqlParameter[] param = new SqlParameter[size];
                param[0] = new SqlParameter("@param1", _run.GetDunningDate());
                param[1] = new SqlParameter("@param2", _run.GetDunningDate());
                param[2] = new SqlParameter("@param3", _run.GetAD_Client_ID());
                param[3] = new SqlParameter("@param4", _run.GetDunningDate());
                param[4] = new SqlParameter("@param5", _run.GetC_DunningLevel_ID());
                if (_C_BPartner_ID != 0)
                {
                    //pstmt.setInt(6, _C_BPartner_ID);
                    param[5] = new SqlParameter("@param6", _C_BPartner_ID);
                }
                else if (_C_BP_Group_ID != 0)
                {
                    //pstmt.setInt(6, _C_BP_Group_ID);
                    param[5] = new SqlParameter("@param6", _C_BP_Group_ID);
                }

                idr = DataBase.DB.ExecuteReader(sql, param, Get_TrxName());
                //
                while (idr.Read())
                {
                    int     C_Invoice_ID  = Utility.Util.GetValueOfInt(idr[0]);
                    int     C_Currency_ID = Utility.Util.GetValueOfInt(idr[1]);
                    Decimal GrandTotal    = Utility.Util.GetValueOfDecimal(idr[2]);
                    Decimal Open          = Utility.Util.GetValueOfDecimal(idr[3]);
                    int     DaysDue       = Utility.Util.GetValueOfInt(idr[4]);
                    bool    IsInDispute   = "Y".Equals(Utility.Util.GetValueOfString(idr[5]));
                    int     C_BPartner_ID = Utility.Util.GetValueOfInt(idr[6]);
                    //
                    // Check for Dispute
                    if (!_IncludeInDispute && IsInDispute)
                    {
                        continue;
                    }
                    // Check the day again based on rulesets
                    if (DaysDue < Utility.Util.GetValueOfInt(DaysAfterDue) && !_level.IsShowAllDue())
                    {
                        continue;
                    }
                    // Check for an open amount
                    if (Env.ZERO.CompareTo(Open) == 0)
                    {
                        continue;
                    }
                    //
                    int timesDunned   = 0;
                    int daysAfterLast = 0;

                    //2nd record set
                    SqlParameter[] param1 = new SqlParameter[2];
                    param1[0] = new SqlParameter("@C_DunningRun_ID", _run.Get_ID());
                    param1[1] = new SqlParameter("@C_Invoice_ID", C_Invoice_ID);
                    idr1      = DataBase.DB.ExecuteReader(sql2, param1, Get_TrxName());
                    //	SubQuery
                    if (idr1.Read())
                    {
                        timesDunned   = Utility.Util.GetValueOfInt(idr1[0]);
                        daysAfterLast = Utility.Util.GetValueOfInt(idr1[1]);
                    }
                    idr1.Close();
                    //	SubQuery

                    // Ensure that Daysbetween Dunning is enforced
                    // Ensure Not ShowAllDue and Not ShowNotDue is selected
                    // PROBLEM: If you have ShowAll activated then DaysBetweenDunning is not working, because we don't know whether
                    //          there is something which we really must Dunn.
                    if (DaysBetweenDunning != 0 && daysAfterLast < DaysBetweenDunning && !_level.IsShowAllDue() && !_level.IsShowNotDue())
                    {
                        continue;
                    }

                    // We don't want to show non due documents
                    if (DaysDue < 0 && !_level.IsShowNotDue())
                    {
                        continue;
                    }

                    // We will minus the timesDunned if this is the DaysBetweenDunning is not fullfilled.
                    // Remember in checkup later we must reset them!
                    // See also checkDunningEntry()
                    if (daysAfterLast < DaysBetweenDunning)
                    {
                        timesDunned = timesDunned * -1;
                    }
                    //
                    CreateInvoiceLine(C_Invoice_ID, C_Currency_ID, GrandTotal, Open,
                                      DaysDue, IsInDispute, C_BPartner_ID,
                                      timesDunned, daysAfterLast);
                    count++;
                }
                idr.Close();
            }
            catch (Exception e)
            {
                if (idr != null)
                {
                    idr.Close();
                }
                if (idr1 != null)
                {
                    idr1.Close();
                }
                log.Log(Level.SEVERE, "addInvoices", e);
            }

            return(count);
        }