コード例 #1
0
        public int GetBatchId()
        {
            MVA009PaymentMethod paym  = new MVA009PaymentMethod(GetCtx(), _paymentMethod, Get_TrxName());
            MVA009Batch         batch = new MVA009Batch(GetCtx(), 0, Get_TrxName());

            batch.SetAD_Client_ID(GetCtx().GetAD_Client_ID());
            batch.SetAD_Org_ID(GetCtx().GetAD_Org_ID());
            batch.SetC_Bank_ID(_C_Bank_ID);
            batch.SetC_BankAccount_ID(_C_BankAccount_ID);
            batch.SetVA009_PaymentMethod_ID(_paymentMethod);
            batch.SetVA009_PaymentRule(paym.GetVA009_PaymentRule());
            batch.SetVA009_PaymentTrigger(paym.GetVA009_PaymentTrigger());
            batch.SetVA009_DocumentDate(DateTime.Now);
            if (!batch.Save())
            {
                batchid = 0;
            }
            return(batch.GetVA009_Batch_ID());
        }
        protected override string DoIt()
        {
            StringBuilder          _sql       = new StringBuilder();
            MVA009Batch            batch      = new MVA009Batch(GetCtx(), GetRecord_ID(), Get_TrxName());
            MVA009PaymentMethod    _paymthd   = null;
            MVA009BatchLineDetails lineDetail = null;
            MVA009BatchLines       line       = null;

            //if (batch.GetVA009_GenerateLines()=="Y")
            //{
            //    msg = Msg.GetMsg(GetCtx(), "VA009_BatchLineAlreadyCreated");
            //    return msg;
            //}
            msg = DeleteBatchLines(_sql, batch.GetVA009_Batch_ID(), GetCtx(), Get_TrxName());
            if (!String.IsNullOrEmpty(msg))
            {
                return(msg);
            }
            MBankAccount _bankacc = new MBankAccount(GetCtx(), batch.GetC_BankAccount_ID(), Get_TrxName());

            decimal dueamt = 0;

            _sql.Clear();
            _sql.Append(@"Select cp.ad_client_id, cp.ad_org_id,CI.C_Bpartner_ID, ci.c_invoice_id, cp.c_invoicepayschedule_id, cp.duedate, 
                          cp.dueamt, cp.discountdate, cp.discountamt,cp.va009_paymentmethod_id,ci.c_currency_id , doc.DocBaseType
                          From C_Invoice CI inner join C_InvoicePaySchedule CP ON CI.c_invoice_id= CP.c_invoice_id INNER JOIN 
                          C_DocType doc ON doc.C_DocType_ID = CI.C_DocType_ID Where ci.ispaid='N' AND cp.va009_ispaid='N' AND cp.C_Payment_ID IS NULL AND
                          CI.IsActive = 'Y' and ci.docstatus in ('CO','CL') AND cp.VA009_ExecutionStatus !='Y' AND CI.AD_Client_ID = " + batch.GetAD_Client_ID()
                        + " AND CI.AD_Org_ID = " + batch.GetAD_Org_ID());

            if (_C_BPartner_ID > 0)
            {
                _sql.Append("  and CI.C_Bpartner_ID=" + _C_BPartner_ID);
            }
            if (_C_invoice_ID > 0)
            {
                _sql.Append("  and CI.C_invoice_ID=" + _C_invoice_ID);
            }
            if (_paySchedule_ID > 0)
            {
                _sql.Append(" AND CP.C_InvoicePaySchedule_ID=" + _paySchedule_ID);
            }
            if (_docType > 0)
            {
                _sql.Append(" ANd CI.C_DocType_ID=" + _docType);
            }
            else
            {
                _sql.Append(" ANd doc.DocBaseType IN ('API' , 'ARI' , 'APC' , 'ARC') ");
            }
            if (_paymentMethod > 0)
            {
                _sql.Append(" And CP.VA009_PaymentMethod_ID=" + _paymentMethod);
                _paymthd = new MVA009PaymentMethod(GetCtx(), _paymentMethod, Get_TrxName());
                _trigger = _paymthd.IsVA009_IsMandate();
            }

            if (_DateDoc_From != null && _DateDoc_To != null)
            {
                _sql.Append(" and cp.duedate BETWEEN  ");
                _sql.Append(GlobalVariable.TO_DATE(_DateDoc_From, true) + " AND ");
                _sql.Append(GlobalVariable.TO_DATE(_DateDoc_To, true));
            }
            else if (_DateDoc_From != null && _DateDoc_To == null)
            {
                _sql.Append(" and cp.duedate >=" + GlobalVariable.TO_DATE(_DateDoc_From, true));
            }
            else if (_DateDoc_From == null && _DateDoc_To != null)
            {
                _sql.Append(" and cp.duedate <=" + GlobalVariable.TO_DATE(_DateDoc_To, true));
            }
            //else if (C_ConversionType_ID > 0) //commented the conversion type because while creatring invoice against Base currency, system will set currencyconversionType_ID=0
            //{
            //    _sql.Append("  AND C_ConversionType_ID=" + C_ConversionType_ID);
            //}
            if (VA009_IsSameCurrency == true)
            {
                _sql.Append(" AND CI.C_Currency_ID =" + _bankacc.GetC_Currency_ID());
            }

            _sql.Append(" Order by CI.C_Bpartner_ID asc , doc.docbasetype ");

            DataSet ds = new DataSet();

            ds = DB.ExecuteDataset(_sql.ToString());
            if (ds.Tables != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                if (C_ConversionType_ID == 0) //to Set Default conversion Type
                {
                    C_ConversionType_ID = GetDefaultConversionType(_sql);
                }
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    if ((Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["DueAmt"])) == 0)
                    {
                        continue;
                    }
                    // if invoice is of AP Invoice and AP Credit Memo then make a single Batch line
                    if (docBaseType == "API" || docBaseType == "APC")
                    {
                        if (_BPartner == Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_BPartner_ID"]) &&
                            ("API" == Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]) || "APC" == Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"])))
                        {
                            line = new MVA009BatchLines(GetCtx(), _VA009_BatchLine_ID, Get_TrxName());
                        }
                        else
                        {
                            line = null;
                        }
                    }
                    // if invoice is of AR Invoice and AR Credit Memo then make a single Batch line
                    else if (docBaseType == "ARI" || docBaseType == "ARC")
                    {
                        if (_BPartner == Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_BPartner_ID"]) &&
                            ("ARI" == Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]) || "ARC" == Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"])))
                        {
                            line = new MVA009BatchLines(GetCtx(), _VA009_BatchLine_ID, Get_TrxName());
                        }
                        else
                        {
                            line = null;
                        }
                    }
                    //if (_BPartner == Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_BPartner_ID"]) && docBaseType == Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]))
                    //{
                    //    line = new MVA009BatchLines(GetCtx(), _VA009_BatchLine_ID, null);
                    //}
                    // else
                    if (line == null)
                    {
                        line = new MVA009BatchLines(GetCtx(), 0, Get_TrxName());
                        line.SetAD_Client_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["Ad_Client_ID"]));
                        line.SetAD_Org_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["Ad_Org_ID"]));
                        line.SetVA009_Batch_ID(batch.GetVA009_Batch_ID());

                        _BPartner   = Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_BPartner_ID"]);
                        docBaseType = Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]);

                        line.SetC_BPartner_ID(_BPartner);
                        if (_trigger == true)
                        {
                            _sql.Clear();
                            _sql.Append("Select VA009_BPMandate_id from C_BPartner Where C_BPartner_ID=" + _BPartner + " AND IsActive = 'Y' AND AD_Client_ID = " + GetAD_Client_ID());
                            DataSet ds1 = new DataSet();
                            ds1 = DB.ExecuteDataset(_sql.ToString());
                            if (ds1.Tables != null && ds1.Tables.Count > 0 && ds1.Tables[0].Rows.Count > 0)
                            {
                                line.SetVA009_BPMandate_ID(Util.GetValueOfInt(ds1.Tables[0].Rows[0]["VA009_BPMandate_id"]));
                            }
                        }
                        if (line.Save(Get_TrxName()))
                        {
                            //line.SetProcessed(true);
                            line.Save(Get_TrxName());
                            _VA009_BatchLine_ID = line.GetVA009_BatchLines_ID();
                        }
                        else
                        {
                            Get_TrxName().Rollback();
                            _BPartner           = 0;
                            _VA009_BatchLine_ID = 0;
                        }
                    }
                    lineDetail = new MVA009BatchLineDetails(GetCtx(), 0, Get_TrxName());
                    lineDetail.SetAD_Client_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["Ad_Client_ID"]));
                    lineDetail.SetAD_Org_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["Ad_Org_ID"]));
                    lineDetail.SetVA009_BatchLines_ID(line.GetVA009_BatchLines_ID());
                    lineDetail.SetC_Invoice_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_Invoice_ID"]));
                    lineDetail.SetC_InvoicePaySchedule_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_InvoicePaySchedule_id"]));
                    lineDetail.SetDueDate(Util.GetValueOfDateTime(ds.Tables[0].Rows[i]["DueDate"]));
                    lineDetail.SetC_ConversionType_ID(C_ConversionType_ID);
                    dueamt = (Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["DueAmt"]));
                    Decimal DiscountAmt = Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["DiscountAmt"]);

                    bool issamme = true; decimal comvertedamt = 0;
                    if (Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_currency_id"]) == _bankacc.GetC_Currency_ID())
                    {
                        issamme = true;
                    }
                    else
                    {
                        issamme = false;
                    }
                    if (!issamme)
                    {
                        dueamt = MConversionRate.Convert(GetCtx(), dueamt, Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_currency_id"]), _bankacc.GetC_Currency_ID(), DateTime.Now, C_ConversionType_ID, GetCtx().GetAD_Client_ID(), GetCtx().GetAD_Org_ID());
                        if (DiscountAmt > 0)
                        {
                            DiscountAmt = MConversionRate.Convert(GetCtx(), DiscountAmt, Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_currency_id"]), _bankacc.GetC_Currency_ID(), DateTime.Now, C_ConversionType_ID, GetCtx().GetAD_Client_ID(), GetCtx().GetAD_Org_ID());
                            if (DiscountAmt == 0)
                            {
                                Get_TrxName().Rollback();
                                msg = Msg.GetMsg(GetCtx(), "NoCurrencyConversion");
                                MCurrency from = new MCurrency(GetCtx(), Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_currency_id"]), Get_TrxName());
                                MCurrency to   = new MCurrency(GetCtx(), Util.GetValueOfInt(_bankacc.GetC_Currency_ID()), Get_TrxName());
                                return(msg + from.GetISO_Code() + "," + to.GetISO_Code());
                            }
                        }
                        if (dueamt == 0)
                        {
                            Get_TrxName().Rollback();
                            msg = Msg.GetMsg(GetCtx(), "NoCurrencyConversion");
                            MCurrency from = new MCurrency(GetCtx(), Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_currency_id"]), Get_TrxName());
                            MCurrency to   = new MCurrency(GetCtx(), Util.GetValueOfInt(_bankacc.GetC_Currency_ID()), Get_TrxName());
                            return(msg + from.GetISO_Code() + "," + to.GetISO_Code());
                        }
                    }

                    if (Util.GetValueOfDateTime(ds.Tables[0].Rows[i]["DiscountDate"]) >= Util.GetValueOfDateTime(batch.GetVA009_DocumentDate()))
                    {
                        //dueamt = dueamt - (Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["DiscountAmt"]));
                        dueamt = dueamt - DiscountAmt;
                        //  145-2.88
                    }



                    if (Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]) == "APC" || Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]) == "ARC")
                    {
                        lineDetail.SetDueAmt(-1 * dueamt);
                        comvertedamt = (-1 * dueamt);
                    }
                    else
                    {
                        lineDetail.SetDueAmt(dueamt);
                        comvertedamt = (dueamt);
                    }
                    if (issamme == false)
                    {
                        comvertedamt = dueamt;
                        //comvertedamt = MConversionRate.Convert(GetCtx(), dueamt, Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_currency_id"]), _bankacc.GetC_Currency_ID(), DateTime.Now, C_ConversionType_ID, GetCtx().GetAD_Client_ID(), GetCtx().GetAD_Org_ID());
                        lineDetail.SetC_Currency_ID(_bankacc.GetC_Currency_ID());
                        if (Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]) == "APC" || Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]) == "ARC")
                        {
                            comvertedamt = (-1 * comvertedamt);
                        }
                    }
                    else
                    {
                        lineDetail.SetC_Currency_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_currency_id"]));
                    }

                    lineDetail.SetVA009_ConvertedAmt(comvertedamt);
                    lineDetail.SetVA009_PaymentMethod_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["va009_paymentmethod_id"]));


                    if (Util.GetValueOfDateTime(ds.Tables[0].Rows[i]["DiscountDate"]) < Util.GetValueOfDateTime(batch.GetVA009_DocumentDate()))
                    {
                        lineDetail.SetDiscountDate(null);
                        lineDetail.SetDiscountAmt(0);
                    }
                    else if (Util.GetValueOfDateTime(ds.Tables[0].Rows[i]["DiscountDate"]) >= Util.GetValueOfDateTime(batch.GetVA009_DocumentDate()))
                    {
                        lineDetail.SetDiscountDate(Util.GetValueOfDateTime(ds.Tables[0].Rows[i]["DiscountDate"]));
                        //lineDetail.SetDiscountAmt(Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["DiscountAmt"]));
                        lineDetail.SetDiscountAmt(DiscountAmt);
                    }

                    if (!lineDetail.Save(Get_TrxName()))
                    {
                        Get_TrxName().Rollback();
                        return(Msg.GetMsg(GetCtx(), "VA009_BatchLineNotCrtd"));
                        //return"BatchLine Not Saved";
                    }
                    else
                    {
                        //lineDetail.SetProcessed(true);
                        //lineDetail.Save(Get_TrxName());
                        //MInvoicePaySchedule _invpay = new MInvoicePaySchedule(GetCtx(), Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_InvoicePaySchedule_id"]), Get_TrxName());
                        //_invpay.SetVA009_ExecutionStatus("Y");
                        //_invpay.Save(Get_TrxName());
                    }
                }
                batch.SetVA009_GenerateLines("Y");
                //batch.SetProcessed(true); //Commeted by Arpit asked by Ashish Gandhi to set processed only if the Payment completion is done
                batch.Save(Get_TrxName());
                if (_paymentMethod != 0)
                {
                    //_paymthd = new MVA009PaymentMethod(GetCtx(), _paymentMethod, Get_TrxName());
                    batch.SetVA009_PaymentMethod_ID(_paymentMethod);
                    batch.SetVA009_PaymentRule(_paymthd.GetVA009_PaymentRule());
                    batch.SetVA009_PaymentTrigger(_paymthd.GetVA009_PaymentTrigger());
                    if (!batch.Save(Get_TrxName()))
                    {
                        Get_TrxName().Rollback();
                        return(Msg.GetMsg(GetCtx(), "VA009_BatchLineNotCrtd"));
                    }
                }
                return(Msg.GetMsg(GetCtx(), "VA009_BatchLineCrtd"));;
            }
            else
            {
                return(Msg.GetMsg(GetCtx(), "VA009_BatchLineNotCrtd"));
            };
        }
コード例 #3
0
        public object GetMethod(int VA009_Batch_ID, Ctx ctx, Trx GetTrx)
        {
            //VA009_DownloadAllHO u = new VA009_DownloadAllHO();
            //u.DownloadMethod(ctx, GetTrx);
            MVA009Batch _batch = new MVA009Batch(ctx, VA009_Batch_ID, GetTrx);
            string      sql = string.Empty; string filename = string.Empty;
            string      foldername = string.Empty; bool module = false;
            int         _VA017 = Util.GetValueOfInt(DB.ExecuteScalar("SELECT COUNT(AD_MODULEINFO_ID) FROM AD_MODULEINFO WHERE PREFIX='VA017_'  AND IsActive = 'Y'", null, GetTrx));

            if (_VA017 > 0)
            {
                foldername = Util.GetValueOfString(DB.ExecuteScalar("SELECT VA017_LocalOpFolder FROM VA017_ICICI_Payment WHERE C_BankAccount_ID=" + _batch.GetC_BankAccount_ID(), null, GetTrx));
                module     = true;
            }
            if (module == true)
            {
                DataSet ds = new DataSet();
                sql = @"SELECT bac.accountno AS Batch_Account_NO,ICI.VA017_OutwardFilePrefix, ICI.VA017_ResponseFilePrefix,  cbp.accountno AS BP_Account_No,  cbp.a_name    AS Account_Name, 
                        bld.dueamt,  pm.VA009_TransferCode as va009_paymentbasetype,  to_date(sysdate,'DD-MON-YYYY')  AS DateTrx,  bank.routingno AS IFSC,  usr.phone ,  cbp.a_email AS Account_Email,
                        bld.c_invoicepayschedule_id,  cbp.a_street  ||cbp.a_city  || cbp.a_state  ||cbp.a_country AS address,  bl.va009_batchlines_id, 
                        bld.va009_batchlinedetails_ID ,  bld.discountamt,  inv.issotrx,  inv.isreturntrx,  bld.ad_org_id,  bld.ad_client_id ,  
                        doc.DocBaseType FROM va009_batchlinedetails bld INNER JOIN va009_batchlines bl ON bl.va009_batchlines_id=bld.va009_batchlines_id 
                        LEFT JOIN c_bp_bankaccount CBP ON cbp.c_bpartner_id=bl.c_bpartner_id INNER JOIN va009_batch b ON b.va009_batch_id =bl.va009_batch_id 
                        INNER JOIN va009_paymentmethod pm ON pm.va009_paymentmethod_id=bld.va009_paymentmethod_id INNER JOIN c_bankaccount bac ON 
                        bac.c_bankaccount_id=b.c_bankaccount_id LEFT JOIN C_Bank Bank ON Bank.c_bank_id=CBP.C_Bank_ID LEFT JOIN ad_user usr ON 
                        usr.c_bpartner_id=bl.c_bpartner_id INNER JOIN c_invoice inv ON inv.c_invoice_id = bld.c_invoice_id INNER JOIN VA017_ICICI_Payment ICI ON ICI.c_bankaccount_id =b.c_bankaccount_id INNER JOIN C_DocType doc ON 
                        doc.C_Doctype_ID= inv.C_Doctype_ID WHERE NVL(bl.c_payment_id , 0) = 0  AND NVL(bld.c_payment_id , 0)  = 0 AND b.va009_batch_id=" + _batch.GetVA009_Batch_ID();
                ds  = null;
                ds  = DB.ExecuteDataset(sql);
                decimal amt = 0;
                if (ds != null && ds.Tables[0].Rows.Count > 0)
                {
                    StringBuilder stre           = new StringBuilder();
                    string        PaymentModeVal = "";
                    stre.Append("DebitAcNo|BeneficiaryAcNo|BeneficiaryName|Amount|PayMode|Date(DD-MMM-YYYY)|IFSC|BeneMobileNo|BeneEmail-Id|PaymnetDetail|BeneficiaryMailingAddress|CreditNarration|BatchDetailID|BankTrxID|PaymentStatus");
                    stre.AppendLine("");
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        if (Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["dueamt"]) != 0)
                        {
                            filename = Util.GetValueOfString(ds.Tables[0].Rows[i]["VA017_OutwardFilePrefix"]) + Util.GetValueOfString(ds.Tables[0].Rows[i]["Batch_Account_NO"]);
                            if (Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]) == "API" || Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]) == "ARC")
                            {
                                if (Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["dueamt"]) < 0)
                                {
                                    amt = Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["dueamt"]) * -1;
                                }
                                else
                                {
                                    amt = Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["dueamt"]);
                                }
                                if (Util.GetValueOfString(ds.Tables[0].Rows[i]["IFSC"]).Contains("ICIC"))
                                {
                                    PaymentModeVal = "I";
                                }
                                else
                                {
                                    PaymentModeVal = Util.GetValueOfString(ds.Tables[0].Rows[i]["va009_paymentbasetype"]);
                                }

                                stre.AppendLine(ds.Tables[0].Rows[i]["Batch_Account_NO"] + "|" + ds.Tables[0].Rows[i]["BP_Account_No"] +
                                                "|" + ds.Tables[0].Rows[i]["Account_Name"] + "|" + amt +
                                                "|" + PaymentModeVal + "|" + String.Format("{0:dd/MM/yy}", ds.Tables[0].Rows[i]["DateTrx"]) +
                                                "|" + ds.Tables[0].Rows[i]["IFSC"] + "|" + ds.Tables[0].Rows[i]["phone"] +
                                                "|" + ds.Tables[0].Rows[i]["Account_Email"] + "|" + ds.Tables[0].Rows[i]["c_invoicepayschedule_id"] +
                                                "|" + ds.Tables[0].Rows[i]["address"] + "|" + ds.Tables[0].Rows[i]["va009_batchlines_id"] +
                                                "|" + ds.Tables[0].Rows[i]["va009_batchlinedetails_ID"]);
                            }
                            else if (Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]) == "ARI" || Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]) == "APC")
                            {
                                if (Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["dueamt"]) < 0)
                                {
                                    amt = Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["dueamt"]) * -1;
                                }
                                else
                                {
                                    amt = Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["dueamt"]);
                                }
                                if (Util.GetValueOfString(ds.Tables[0].Rows[i]["IFSC"]) == "ICIC")
                                {
                                    PaymentModeVal = "I";
                                }
                                else
                                {
                                    PaymentModeVal = Util.GetValueOfString(ds.Tables[0].Rows[i]["va009_paymentbasetype"]);
                                }
                                stre.AppendLine(ds.Tables[0].Rows[i]["BP_Account_No"] + "|" + ds.Tables[0].Rows[i]["Batch_Account_NO"] +
                                                "|" + ds.Tables[0].Rows[i]["Account_Name"] + "|" + amt +
                                                "|" + PaymentModeVal + "|" + String.Format("{0:dd/MM/yy}", ds.Tables[0].Rows[i]["DateTrx"]) +
                                                "|" + ds.Tables[0].Rows[i]["IFSC"] + "|" + ds.Tables[0].Rows[i]["phone"] +
                                                "|" + ds.Tables[0].Rows[i]["Account_Email"] + "|" + ds.Tables[0].Rows[i]["c_invoicepayschedule_id"] +
                                                "|" + ds.Tables[0].Rows[i]["address"] + "|" + ds.Tables[0].Rows[i]["va009_batchlines_id"] +
                                                "|" + ds.Tables[0].Rows[i]["va009_batchlinedetails_ID"]);
                            }
                        }
                    }
                    filename += "_" + _batch.GetVA009_Batch_ID() + "_" + DateTime.Now.Day + "-" + DateTime.Now.Month + "-" + DateTime.Now.Year + "_" + DateTime.Now.Hour + "-" + DateTime.Now.Minute + "-" + DateTime.Now.Second;
                    CreateLocalCSVFile(stre.ToString(), filename, foldername, module);
                }
                return(Msg.GetMsg(ctx, "VA009_FileCreated"));
            }
            else
            {
                return(Msg.GetMsg(ctx, "VA009_ICICIModuleNotInstalled"));
            }
        }
コード例 #4
0
        public string CreateJDBCDataSourceXml(Ctx ctx, string DBResourceName, int RecordID)
        {
            List <Document> obj     = new List <Document>();
            string          orgName = "Org Name";

            if (RecordID > 0)
            {
                MVA009Batch            batch        = new MVA009Batch(ctx, RecordID, null);
                MVA009BatchLineDetails batchDetails = null;
                MVA009PaymentMethod    paymthd      = null;
                string  sql = @"SELECT bd.VA009_BatchLineDetails_ID
                                FROM VA009_BatchLineDetails bd
                                INNER JOIN va009_batchlines bl
                                ON bl.va009_batchlines_id= bd.va009_batchlines_id
                                WHERE VA009_Batch_ID =" + RecordID;
                DataSet dsb = DB.ExecuteDataset(sql);
                if (dsb != null && dsb.Tables[0].Rows.Count > 0)
                {
                    DataSet dsbnk = null;
                    for (int x = 0; x < dsb.Tables[0].Rows.Count; x++)
                    {
                        batchDetails = new MVA009BatchLineDetails(ctx, Util.GetValueOfInt(dsb.Tables[0].Rows[x]["VA009_BatchLineDetails_ID"]), null);
                        paymthd      = new MVA009PaymentMethod(ctx, batchDetails.GetVA009_PaymentMethod_ID(), null);
                        Document dataobj = new Document();
                        dataobj.PaymentID     = Util.GetValueOfInt(batchDetails.GetVA009_BatchLineDetails_ID());
                        dataobj.PaymentMethod = paymthd.GetVA009_Name();
                        dataobj.DueAmt        = batchDetails.GetDueAmt();
                        dataobj.currency      = Util.GetValueOfString(DB.ExecuteScalar("SELECT iso_code FROM c_currency WHERE c_currency_id = " + batchDetails.GetC_Currency_ID()));
                        dataobj.Org           = Util.GetValueOfString(DB.ExecuteScalar("SELECT name FROM ad_org WHERE ad_org_id = " + batchDetails.GetAD_Org_ID()));
                        dsbnk = DB.ExecuteDataset("SELECT b.RoutingNo,  ac.IBAN FROM C_Bank b INNER JOIN C_bankaccount ac ON b.c_bank_id= ac.c_bank_id where ac.c_bankaccount_id =" + batch.GetC_BankAccount_ID());
                        if (dsbnk != null && dsbnk.Tables[0].Rows.Count > 0)
                        {
                            dataobj.IBAN = Util.GetValueOfString(dsbnk.Tables[0].Rows[0]["IBAN"]);
                            dataobj.BIC  = Util.GetValueOfString(dsbnk.Tables[0].Rows[0]["RoutingNo"]);
                        }
                        orgName = dataobj.Org;
                        obj.Add(dataobj);
                    }
                }
            }
            //Get DataBase Details of Vienna Advantage Framework
            string      filePath = "";
            VConnection conn     = new VConnection();

            conn.SetAttributes(DBConn.CreateConnectionString());
            DataSet ds = new DataSet();

            if (conn.Db_Type == "Oracle")
            {
                #region commented
                //Document obj = new Document();
                //obj.AccountName = "Manjot";
                //obj.AccountNo = "1001";
                //objJDBC.exportedWithPermissions = "false";
                //objJDBC.folder = "/organizations";
                //objJDBC.name = DBResourceName;
                //objJDBC.version = "0";
                //objJDBC.label = DBResourceName;
                //objJDBC.creationDate = DateTime.Now.ToString("O");
                //objJDBC.updateDate = DateTime.Now.ToString("O");
                //objJDBC.driver = "tibcosoftware.jdbc.oracle.OracleDriver";
                //objJDBC.connectionUrl = "jdbc:tibcosoftware:oracle://" + conn.Db_host + ";SID=" + conn.Db_name;
                //objJDBC.connectionUser = conn.Db_uid;
                //objJDBC.connectionPassword = conn.Db_pwd;
                //objJDBC.timezone = "";
                #endregion

                filePath = XmlCreater(obj, true, DBResourceName, orgName);
                return(filePath);
            }
            return(filePath);
        }
        protected override void Prepare()
        {
            MVA009Batch _batch = new MVA009Batch(GetCtx(), GetRecord_ID(), Get_TrxName());

            className = Util.GetValueOfString(DB.ExecuteScalar("SELECT pc.va009_name FROM va009_bankpaymentclass bpc INNER JOIN va009_paymentclass pc ON bpc.va009_paymentclass_id=pc.va009_paymentclass_id WHERE bpc.c_bankaccount_id=" + _batch.GetC_BankAccount_ID() + " AND bpc.ad_client_id=" + GetCtx().GetAD_Client_ID() + "", null, Get_TrxName()));
        }
コード例 #6
0
        protected override string DoIt()
        {
            batchid = GetBatchId();
            if (batchid > 0)
            {
                StringBuilder          _sql       = new StringBuilder();
                MVA009Batch            batch      = new MVA009Batch(GetCtx(), batchid, Get_TrxName());
                MBankAccount           _bankacc   = new MBankAccount(GetCtx(), batch.GetC_BankAccount_ID(), Get_TrxName());
                MVA009PaymentMethod    _paymthd   = null;
                MVA009BatchLines       line       = null;
                MVA009BatchLineDetails lineDetail = null;
                decimal dueamt = 0;
                _sql.Clear();
                _sql.Append(@"Select cp.ad_client_id, cp.ad_org_id,CI.C_Bpartner_ID, ci.c_invoice_id, cp.c_invoicepayschedule_id, cp.duedate, 
                              cp.dueamt, cp.discountdate, cp.discountamt,cp.va009_paymentmethod_id,ci.c_currency_id , doc.DocBaseType
                              From C_Invoice CI inner join C_InvoicePaySchedule CP ON CI.c_invoice_id= CP.c_invoice_id
                              INNER JOIN C_DocType doc ON doc.C_DocType_ID = CI.C_DocType_ID  Where ci.ispaid='N' AND cp.va009_ispaid='N' AND cp.C_Payment_ID IS NULL
                              AND CI.IsActive = 'Y' and ci.docstatus in ('CO','CL') AND cp.VA009_ExecutionStatus NOT IN ( 'Y','J') AND CI.AD_Client_ID = " + GetAD_Client_ID() + " AND CI.AD_Org_ID = " + GetAD_Org_ID());

                if (_C_BPartner_ID > 0)
                {
                    _sql.Append("  and CI.C_Bpartner_ID=" + _C_BPartner_ID);
                }
                if (_C_invoice_ID > 0)
                {
                    _sql.Append("  and CI.C_invoice_ID=" + _C_invoice_ID);
                }
                if (_paySchedule_ID > 0)
                {
                    _sql.Append(" AND CP.C_InvoicePaySchedule_ID=" + _paySchedule_ID);
                }
                if (_docType > 0)
                {
                    _sql.Append(" ANd CI.C_DocType_ID=" + _docType);
                }
                else
                {
                    _sql.Append(" ANd doc.DocBaseType IN ('API' , 'ARI' , 'APC' , 'ARC') ");
                }
                if (_paymentMethod > 0)
                {
                    _sql.Append(" And CP.VA009_PaymentMethod_ID=" + _paymentMethod);
                    _paymthd = new MVA009PaymentMethod(GetCtx(), _paymentMethod, Get_TrxName());
                    _trigger = _paymthd.IsVA009_IsMandate();
                }

                if (_DateDoc_From != null && _DateDoc_To != null)
                {
                    _sql.Append(" and cp.duedate BETWEEN  ");
                    _sql.Append(GlobalVariable.TO_DATE(_DateDoc_From, true) + " AND ");
                    _sql.Append(GlobalVariable.TO_DATE(_DateDoc_To, true));
                }
                else if (_DateDoc_From != null && _DateDoc_To == null)
                {
                    _sql.Append(" and cp.duedate >=" + GlobalVariable.TO_DATE(_DateDoc_From, true));
                }
                else if (_DateDoc_From == null && _DateDoc_To != null)
                {
                    _sql.Append(" and cp.duedate <=" + GlobalVariable.TO_DATE(_DateDoc_To, true));
                }
                else if (C_ConversionType_ID > 0)
                {
                    _sql.Append("  AND C_ConversionType_ID=" + C_ConversionType_ID);
                }

                if (VA009_IsSameCurrency == true)
                {
                    _sql.Append(" AND CI.C_Currency_ID =" + _bankacc.GetC_Currency_ID());
                }

                _sql.Append(" Order by CI.C_Bpartner_ID asc , doc.docbasetype ");

                DataSet ds = new DataSet();
                ds = DB.ExecuteDataset(_sql.ToString(), null, Get_TrxName());
                if (ds.Tables != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        if ((Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["DueAmt"])) == 0)
                        {
                            continue;
                        }
                        // if invoice is of AP Invoice and AP Credit Memo then make a single Batch line
                        if (docBaseType == "API" || docBaseType == "APC")
                        {
                            if (_BPartner == Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_BPartner_ID"]) &&
                                ("API" == Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]) || "APC" == Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"])))
                            {
                                line = new MVA009BatchLines(GetCtx(), _VA009_BatchLine_ID, Get_TrxName());
                            }
                            else
                            {
                                line = null;
                            }
                        }
                        // if invoice is of AR Invoice and AR Credit Memo then make a single Batch line
                        else if (docBaseType == "ARI" || docBaseType == "ARC")
                        {
                            if (_BPartner == Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_BPartner_ID"]) &&
                                ("ARI" == Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]) || "ARC" == Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"])))
                            {
                                line = new MVA009BatchLines(GetCtx(), _VA009_BatchLine_ID, Get_TrxName());
                            }
                            else
                            {
                                line = null;
                            }
                        }
                        //if (_BPartner == Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_BPartner_ID"]) && docBaseType == Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]))
                        //{
                        //    line = new MVA009BatchLines(GetCtx(), _VA009_BatchLine_ID, null);
                        //}
                        // else
                        if (line == null)
                        {
                            line = new MVA009BatchLines(GetCtx(), 0, Get_TrxName());
                            line.SetAD_Client_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["Ad_Client_ID"]));
                            line.SetAD_Org_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["Ad_Org_ID"]));
                            line.SetVA009_Batch_ID(batch.GetVA009_Batch_ID());

                            _BPartner   = Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_BPartner_ID"]);
                            docBaseType = Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]);

                            line.SetC_BPartner_ID(_BPartner);
                            if (_trigger == true)
                            {
                                _sql.Clear();
                                _sql.Append("Select VA009_BPMandate_id from C_BPartner Where C_BPartner_ID=" + _BPartner + " AND IsActive = 'Y' AND AD_Client_ID = " + GetAD_Client_ID());
                                DataSet ds1 = new DataSet();
                                ds1 = DB.ExecuteDataset(_sql.ToString(), null, Get_TrxName());
                                if (ds1.Tables != null && ds1.Tables.Count > 0 && ds1.Tables[0].Rows.Count > 0)
                                {
                                    line.SetVA009_BPMandate_ID(Util.GetValueOfInt(ds1.Tables[0].Rows[0]["VA009_BPMandate_id"]));
                                }
                            }
                            if (line.Save())
                            {
                                line.SetProcessed(true);
                                line.Save();
                                _VA009_BatchLine_ID = line.GetVA009_BatchLines_ID();
                            }
                            else
                            {
                                _BPartner           = 0;
                                _VA009_BatchLine_ID = 0;
                            }
                        }
                        lineDetail = new MVA009BatchLineDetails(GetCtx(), 0, Get_TrxName());
                        lineDetail.SetAD_Client_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["Ad_Client_ID"]));
                        lineDetail.SetAD_Org_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["Ad_Org_ID"]));
                        lineDetail.SetVA009_BatchLines_ID(line.GetVA009_BatchLines_ID());
                        lineDetail.SetC_Invoice_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_Invoice_ID"]));
                        lineDetail.SetC_InvoicePaySchedule_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_InvoicePaySchedule_id"]));
                        lineDetail.SetDueDate(Util.GetValueOfDateTime(ds.Tables[0].Rows[i]["DueDate"]));
                        lineDetail.SetC_ConversionType_ID(C_ConversionType_ID);
                        dueamt = (Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["DueAmt"]));
                        if (Util.GetValueOfDateTime(ds.Tables[0].Rows[i]["DiscountDate"]) >= Util.GetValueOfDateTime(batch.GetVA009_DocumentDate()))
                        {
                            dueamt = dueamt - (Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["DiscountAmt"]));
                            //  145-2.88
                        }

                        bool issamme = true; decimal comvertedamt = 0;
                        if (Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_currency_id"]) == _bankacc.GetC_Currency_ID())
                        {
                            issamme = true;
                        }
                        else
                        {
                            issamme = false;
                        }

                        if (Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]) == "APC" || Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]) == "ARC")
                        {
                            lineDetail.SetDueAmt(-1 * dueamt);
                            comvertedamt = (-1 * dueamt);
                        }
                        else
                        {
                            lineDetail.SetDueAmt(dueamt);
                            comvertedamt = (dueamt);
                        }
                        if (issamme == false)
                        {
                            comvertedamt = 0;
                            comvertedamt = MConversionRate.Convert(GetCtx(), dueamt, Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_currency_id"]), _bankacc.GetC_Currency_ID(), DateTime.Now, C_ConversionType_ID, GetCtx().GetAD_Client_ID(), GetCtx().GetAD_Org_ID());
                            lineDetail.SetC_Currency_ID(_bankacc.GetC_Currency_ID());
                            if (Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]) == "APC" || Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]) == "ARC")
                            {
                                comvertedamt = (-1 * comvertedamt);
                            }
                        }
                        else
                        {
                            lineDetail.SetC_Currency_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_currency_id"]));
                        }

                        lineDetail.SetVA009_ConvertedAmt(comvertedamt);
                        lineDetail.SetVA009_PaymentMethod_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["va009_paymentmethod_id"]));
                        if (Util.GetValueOfDateTime(ds.Tables[0].Rows[i]["DiscountDate"]) < Util.GetValueOfDateTime(batch.GetVA009_DocumentDate()))
                        {
                            lineDetail.SetDiscountDate(null);
                            lineDetail.SetDiscountAmt(0);
                        }
                        else if (Util.GetValueOfDateTime(ds.Tables[0].Rows[i]["DiscountDate"]) >= Util.GetValueOfDateTime(batch.GetVA009_DocumentDate()))
                        {
                            lineDetail.SetDiscountDate(Util.GetValueOfDateTime(ds.Tables[0].Rows[i]["DiscountDate"]));
                            lineDetail.SetDiscountAmt(Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["DiscountAmt"]));
                        }

                        if (!lineDetail.Save())
                        {
                            //return"BatchLine Not Saved";
                        }
                        else
                        {
                            lineDetail.SetProcessed(true);
                            MInvoicePaySchedule _invpay = new MInvoicePaySchedule(GetCtx(), Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_InvoicePaySchedule_id"]), Get_TrxName());
                            _invpay.SetVA009_ExecutionStatus("Y");
                            _invpay.Save();
                            lineDetail.Save();
                        }
                    }
                    batch.SetVA009_GenerateLines("Y");
                    batch.SetProcessed(true);
                    batch.Save();
                    if (_paymentMethod != 0)
                    {
                        //_paymthd = new MVA009PaymentMethod(GetCtx(), _paymentMethod, Get_TrxName());
                        batch.SetVA009_PaymentMethod_ID(_paymentMethod);
                        batch.SetVA009_PaymentRule(_paymthd.GetVA009_PaymentRule());
                        batch.SetVA009_PaymentTrigger(_paymthd.GetVA009_PaymentTrigger());
                        batch.Save();
                        if (_paymthd.GetVA009_PaymentRule() == "M")
                        {
                            VA009_CreatePayments payment = new VA009_CreatePayments();
                            payment.DoIt(batch.GetVA009_Batch_ID(), GetCtx(), Get_TrxName(), 0);
                        }
                        else if (_paymthd.GetVA009_PaymentRule() == "E")
                        {
                            VA009_ICICI_Snorkel _Snrkl = new VA009_ICICI_Snorkel();
                            _Snrkl.GetMethod(batch.GetVA009_Batch_ID(), GetCtx(), Get_TrxName());
                        }
                    }
                    return(Msg.GetMsg(GetCtx(), "VA009_BatchLineCrtd"));;
                }
                else
                {
                    DB.ExecuteQuery("DELETE FROM VA009_Batch WHERE VA009_Batch_ID=" + batchid, null, Get_TrxName());

                    return(Msg.GetMsg(GetCtx(), "VA009_BatchLineNotCrtd"));
                }
            }
            else
            {
                DB.ExecuteQuery("DELETE FROM VA009_Batch WHERE VA009_Batch_ID=" + batchid, null, Get_TrxName());

                return(Msg.GetMsg(GetCtx(), "VA009_BatchNotCrtd"));
            }
        }