public String DeleteAttributeValue(VA005_DeleteAttributeValue value) { MAttributeValue obj = new MAttributeValue(_ctx, value.attributeID, null); int attvalid = obj.GetM_AttributeValue_ID(); string _result = ""; //try //{ if (!obj.Delete(true)) { ValueNamePair pp = VLogger.RetrieveError(); _result = pp.ToString(); } //} //catch(Exception e) //{ // return e.Message; //} //if (_result) //{ // //return Msg.GetMsg(_ctx,"VA005_Deleted"); // return attvalid.ToString(); //} //else //{ //return Msg.GetMsg(_ctx,"VA005_NotDeleted"); //} return(_result); }
public string DeleteAttributeSetValue(VA005_DeleteAttributeSetValue value) { //MAttributeValue obj = new MAttributeValue(_ctx, value.attributesetdelID, null); MAttributeSet obj = new MAttributeSet(_ctx, value.attributesetdelID, null); int attvalid = obj.GetM_AttributeSet_ID(); string _result = ""; if (!obj.Delete(true)) { ValueNamePair pp = VLogger.RetrieveError(); _result = pp.ToString(); } return(_result); }
//public string DeleteAttributeSetValue(VA005_DeleteAttributeSetValue value) //{ // //MAttributeValue obj = new MAttributeValue(_ctx, value.attributesetdelID, null); // MAttributeSet obj = new MAttributeSet(_ctx, value.attributesetdelID, null); // int attvalid = obj.GetM_AttributeSet_ID(); // bool _result = false; // try // { // _result = obj.Delete(true); // } // catch (Exception e) // { // return e.Message; // } // if (_result) // { // //return Msg.GetMsg(_ctx,"VA005_Deleted"); // return attvalid.ToString(); // } // else // { // return Msg.GetMsg(_ctx, "VA005_NotDeleted"); // } //} public string DeleteAttributeSetValue(List <Int32> value) { string _result = ""; for (int i = 0; i < value.Count; i++) { MAttribute obj = new MAttribute(_ctx, value[i], null); int attvalid = obj.GetM_Attribute_ID(); try { if (!obj.Delete(true)) { ValueNamePair pp = VLogger.RetrieveError(); _result += pp.ToString() + "/n"; } } catch (Exception e) { } } return(_result); }
/// <summary> /// Create actual Payment /// </summary> /// <param name="C_Invoice_ID">invoice</param> /// <param name="C_BPartner_ID">partner ignored when invoice exists</param> /// <param name="C_Currency_ID">currency</param> /// <param name="stmtAmt">statement amount</param> /// <param name="trxAmt">transaction amt</param> /// <param name="C_BankAccount_ID">bank account</param> /// <param name="dateTrx">transaction date</param> /// <param name="dateAcct">accounting date</param> /// <param name="description">description</param> /// <param name="AD_Org_ID"></param> /// <param name="C_ConversionType_ID">C_ConversionType_ID</param> /// <param name="C_Order_ID">C_Order_ID</param> /// <returns>payment</returns> private MPayment CreatePayment(int C_Invoice_ID, int C_BPartner_ID, int C_Currency_ID, Decimal stmtAmt, Decimal trxAmt, int C_BankAccount_ID, DateTime?dateTrx, DateTime?dateAcct, String description, int AD_Org_ID, int C_ConversionType_ID, int C_Order_ID) { // Trx Amount = Payment overwrites Statement Amount if defined Decimal payAmt = trxAmt; if (Env.ZERO.CompareTo(payAmt) == 0) { payAmt = stmtAmt; } if (C_Invoice_ID == 0 && C_Order_ID == 0 && (Env.ZERO.CompareTo(payAmt) == 0)) { throw new Exception("@PayAmt@ = 0"); } //if (payAmt == null) //{ // payAmt = Env.ZERO; //} // MPayment payment = new MPayment(GetCtx(), 0, Get_Trx()); payment.SetAD_Org_ID(AD_Org_ID); payment.SetC_BankAccount_ID(C_BankAccount_ID); //payment.SetTenderType(MPayment.TENDERTYPE_Check);//not required it will update on MPayment class //Get the C_ConversionType_ID from BankStatementLine and Set C_ConversionType_ID for Payment payment.SetC_ConversionType_ID(C_ConversionType_ID); if (dateTrx != null) { payment.SetDateTrx(dateTrx); } else if (dateAcct != null) { payment.SetDateTrx(dateAcct); } if (dateAcct != null) { payment.SetDateAcct(dateAcct); } else { payment.SetDateAcct(payment.GetDateTrx()); } payment.SetDescription(description); // if (C_Invoice_ID != 0) { MInvoice invoice = new MInvoice(GetCtx(), C_Invoice_ID, Get_Trx()); //Used Trx payment.SetC_DocType_ID(invoice.IsSOTrx()); // Receipt payment.SetC_BPartner_ID(invoice.GetC_BPartner_ID()); //set the BPartner Location from the Invoice payment.SetC_BPartner_Location_ID(invoice.GetC_BPartner_Location_ID()); //set the PaymentMethod by the reference of Invoice payment.SetVA009_PaymentMethod_ID(invoice.GetVA009_PaymentMethod_ID()); payment.SetC_Currency_ID(invoice.GetC_Currency_ID()); decimal _dueAmt = 0; string _sql = "SELECT * FROM C_InvoicePaySchedule WHERE VA009_PAIDAMNT IS NULL AND VA009_IsPaid = 'N' AND IsActive = 'Y' AND C_Invoice_ID =" + invoice.GetC_Invoice_ID(); DataSet _ds = DB.ExecuteDataset(_sql, null, Get_Trx()); if (_ds != null && _ds.Tables[0].Rows.Count == 1) { payment.SetC_Invoice_ID(invoice.GetC_Invoice_ID());//set Invoice Reference payment.SetC_InvoicePaySchedule_ID(Util.GetValueOfInt(_ds.Tables[0].Rows[0]["C_INVOICEPAYSCHEDULE_ID"])); _dueAmt = Util.GetValueOfDecimal(_ds.Tables[0].Rows[0]["DUEAMT"]); //Set PayAmt -ve sign Incase of DocBaseType APC or ARC - IsReturnTrx is true //Set PayAmt +ve sign Incase of DocBaseType API or ARI - IsReturnTrx is false if (!invoice.IsReturnTrx()) { payment.SetPayAmt(_dueAmt); } else // payment is likely to be negative { payment.SetPayAmt(Decimal.Negate(_dueAmt)); } if (!payment.Save(Get_Trx())) { Get_Trx().Rollback(); ValueNamePair pp = VLogger.RetrieveError(); //to get Exact Error Message first get Value from GetName() else GetValue() string error = pp != null?pp.GetName() : ""; if (string.IsNullOrEmpty(error)) { error = pp != null?pp.GetValue() : ""; if (string.IsNullOrEmpty(error)) { error = pp != null?pp.ToString() : ""; } } _message = !string.IsNullOrEmpty(error) ? error : "VA012_PaymentNotSaved"; return(null); } } else if (_ds != null && _ds.Tables[0].Rows.Count > 1) { if (!payment.Save(Get_Trx())) { Get_Trx().Rollback(); ValueNamePair pp = VLogger.RetrieveError(); //to get Exact Error Message first get Value from GetName() else GetValue() string error = pp != null?pp.GetName() : ""; if (string.IsNullOrEmpty(error)) { error = pp != null?pp.GetValue() : ""; if (string.IsNullOrEmpty(error)) { error = pp != null?pp.ToString() : ""; } } _message = !string.IsNullOrEmpty(error) ? error : "VA012_PaymentNotSaved"; return(null); } else { //Initialize the Object for MPaymentAllocate class MPaymentAllocate PayAlocate = null; for (int i = 0; _ds.Tables[0].Rows.Count > i; i++) { //Create the Object for MPaymentAllocate class for every Iteration PayAlocate = new MPaymentAllocate(GetCtx(), 0, Get_Trx()); PayAlocate.SetC_Payment_ID(payment.GetC_Payment_ID()); PayAlocate.SetAD_Client_ID(payment.GetAD_Client_ID()); //set Organization with the reference of Bank Account PayAlocate.SetAD_Org_ID(payment.GetAD_Org_ID());//set Org_ID from the Header PayAlocate.SetC_Invoice_ID(invoice.GetC_Invoice_ID()); PayAlocate.SetC_InvoicePaySchedule_ID(Util.GetValueOfInt(_ds.Tables[0].Rows[i]["C_INVOICEPAYSCHEDULE_ID"])); //Set PayAmt -ve sign Incase of DocBaseType APC or ARC - IsReturnTrx is true //Set PayAmt +ve sign Incase of DocBaseType API or ARI - IsReturnTrx is false if (!invoice.IsReturnTrx()) { PayAlocate.SetInvoiceAmt(Util.GetValueOfDecimal(_ds.Tables[0].Rows[i]["DUEAMT"])); PayAlocate.SetAmount(Util.GetValueOfDecimal(_ds.Tables[0].Rows[i]["DUEAMT"])); } else { PayAlocate.SetInvoiceAmt(-1 * Util.GetValueOfDecimal(_ds.Tables[0].Rows[i]["DUEAMT"])); PayAlocate.SetAmount(-1 * Util.GetValueOfDecimal(_ds.Tables[0].Rows[i]["DUEAMT"])); } if (!PayAlocate.Save(Get_Trx())) { Get_Trx().Rollback(); ValueNamePair pp = VLogger.RetrieveError(); //to get Exact Error Message first get Value from GetName() else GetValue() string error = pp != null?pp.GetName() : ""; if (string.IsNullOrEmpty(error)) { error = pp != null?pp.GetValue() : ""; if (string.IsNullOrEmpty(error)) { error = pp != null?pp.ToString() : ""; } } _message = !string.IsNullOrEmpty(error) ? error : "VA012_PaymentNotSaved"; return(null); } } } } } else if (C_Order_ID > 0 && C_BPartner_ID > 0) // Create Payment for prePayOrder reference { MOrder order = new MOrder(GetCtx(), C_Order_ID, Get_Trx()); payment.SetC_Order_ID(C_Order_ID); payment.SetC_DocType_ID(order.IsSOTrx()); // Receipt payment.SetC_BPartner_ID(order.GetC_BPartner_ID()); payment.SetC_BPartner_Location_ID(order.GetC_BPartner_Location_ID()); payment.SetC_Currency_ID(order.GetC_Currency_ID()); payment.SetVA009_PaymentMethod_ID(order.GetVA009_PaymentMethod_ID()); payment.SetPayAmt(order.GetGrandTotal()); if (!payment.Save(Get_Trx())) { Get_Trx().Rollback(); ValueNamePair pp = VLogger.RetrieveError(); //to get Exact Error Message first get Value from GetName() else GetValue() string error = pp != null?pp.GetName() : ""; if (string.IsNullOrEmpty(error)) { error = pp != null?pp.GetValue() : ""; if (string.IsNullOrEmpty(error)) { error = pp != null?pp.ToString() : ""; } } _message = !string.IsNullOrEmpty(error) ? error : "VA012_PaymentNotSaved"; return(null); } } else { _message = "VA012_ReferenceNotfoundtoCreatePayment"; return(null); } //Commit the Transaction Get_Trx().Commit(); //Call Complete Method to Complete the Record //149 is AD_Process_ID for C_Payment_Process _message = CompletePayment(GetCtx(), payment.GetC_Payment_ID(), 149, MPayment.DOCACTION_Complete); if (!string.IsNullOrEmpty(_message)) { return(null); } return(payment); }
/// <summary> /// Create Payment for BankStatement /// </summary> /// <param name="bsl">bank statement Line</param> /// <returns>Message</returns> private String CreatePayment(MBankStatementLine bsl) { if (bsl == null || bsl.GetC_Payment_ID() != 0) { return("--"); } log.Fine(bsl.ToString()); if (bsl.GetC_Invoice_ID() == 0 && bsl.GetC_BPartner_ID() == 0) { throw new Exception("@NotFound@ @C_Invoice_ID@ / @C_BPartner_ID@"); } // MBankStatement bs = new MBankStatement(GetCtx(), bsl.GetC_BankStatement_ID(), Get_Trx()); // //Check the Column Exists or not then Pass the Colums as parameters for CreatePayment method int conversionType = bsl.Get_ColumnIndex("C_ConversionType_ID") > 0 ? bsl.Get_ValueAsInt("C_ConversionType_ID") : 0; int _order_Id = bsl.Get_ColumnIndex("C_Order_ID") > 0 ? bsl.Get_ValueAsInt("C_Order_ID") : 0; MPayment payment = CreatePayment(bsl.GetC_Invoice_ID(), bsl.GetC_BPartner_ID(), bsl.GetC_Currency_ID(), bsl.GetStmtAmt(), bsl.GetTrxAmt(), bs.GetC_BankAccount_ID(), bsl.GetStatementLineDate(), bsl.GetDateAcct(), bsl.GetDescription(), bsl.GetAD_Org_ID(), conversionType, _order_Id); if (payment == null && !string.IsNullOrEmpty(_message)) { return(_message); } //Update Bank StatementLine //Created new Object for Payment to get Updated Payment Record MPayment mPayment = new MPayment(GetCtx(), payment.GetC_Payment_ID(), payment.Get_Trx()); _message = bsl.SetPayments(mPayment); if (string.IsNullOrEmpty(_message)) { if (!bsl.Save(Get_Trx())) { Get_Trx().Rollback(); ValueNamePair pp = VLogger.RetrieveError(); //to get Exact Error Message first get Value from GetName() else GetValue() string error = pp != null?pp.GetName() : ""; if (string.IsNullOrEmpty(error)) { error = pp != null?pp.GetValue() : ""; if (string.IsNullOrEmpty(error)) { error = pp != null?pp.ToString() : ""; } } return(!string.IsNullOrEmpty(error) ? error : "DatanotSaved"); } else { String retString = "@C_Payment_ID@ = " + mPayment.GetDocumentNo(); if (Env.Signum(payment.GetOverUnderAmt()) != 0) { retString += " - @OverUnderAmt@=" + mPayment.GetOverUnderAmt(); } Get_Trx().Commit(); return(retString); } } else { Get_Trx().Rollback(); return(_message); } }
public string DoIt(int recordID, Ctx ct, Trx trx, int CurrencyType_ID) { //Check Bank Response // sql.Append(@"SELECT count(bd.VA009_BankResponse) FROM va009_batchlinedetails bd INNER JOIN va009_batchlines bl ON bl.va009_batchlines_id=bd.va009_batchlines_id // WHERE bl.va009_batch_id=" + recordID + " AND bd.VA009_BankResponse='IP' AND bd.AD_Client_ID = " + ct.GetAD_Client_ID() + " Group by bd.VA009_BankResponse "); // countresponse = Util.GetValueOfInt(DB.ExecuteScalar(sql.ToString(), null,trx)); sql.Clear(); sql.Append(@"SELECT b.c_bankaccount_id, bl.c_bpartner_id, bld.c_currency_id, bld.c_invoice_id, bld.dueamt, bld.VA009_ConvertedAmt, bld.discountamt, bld.va009_batchlinedetails_ID , bl.va009_batchlines_id , bld.discountdate, inv.issotrx, inv.isreturntrx, bld.c_invoicepayschedule_id, bld.ad_org_id, bld.ad_client_id , doc.DocBaseType , bld.va009_paymentmethod_id , bl.VA009_DueAmount FROM va009_batchlinedetails bld INNER JOIN va009_batchlines bl ON bl.va009_batchlines_id=bld.va009_batchlines_id INNER JOIN va009_batch b ON b.va009_batch_id =bl.va009_batch_id INNER JOIN c_invoice inv ON inv.c_invoice_id = bld.c_invoice_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 NVL(bld.C_AllocationHdr_ID , 0) = 0 AND b.va009_batch_id =" + recordID); if (IsBankresponse == "Y" && countresponse == 0) { sql.Append(" AND bld.va009_bankresponse='RE' ORDER BY bl.c_bpartner_id ASC "); } else if (IsBankresponse == "N") { sql.Append(" ORDER BY bld.va009_batchlines_id , bl.c_bpartner_id ASC "); } //else if (IsBankresponse == "Y" && countresponse > 0) // return Msg.GetMsg(ct, "VA009_AllResponseNotAvailable"); DataSet ds = DB.ExecuteDataset(sql.ToString(), null, trx); MVA009Batch _batch = new MVA009Batch(ct, recordID, trx); if (ds != null && ds.Tables[0].Rows.Count > 0) { #region Consolidate = true if (_batch.IsVA009_Consolidate() == true) { int c_currency_id = 0; int Bpartner_ID = 0; int C_Payment_ID = 0, batchline_id = 0, allocationHeader = 0; for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { #region Create View Allocation Header and line when the Due Amount on Batch line = 0 if (c_currency_id == Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_currency_id"]) && Bpartner_ID == Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_bpartner_id"]) && batchline_id == Util.GetValueOfInt(ds.Tables[0].Rows[i]["va009_batchlines_id"]) && Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["VA009_DueAmount"]) == 0) { MAllocationLine alloclne = new MAllocationLine(ct, 0, trx); alloclne.SetAD_Client_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_client_id"])); alloclne.SetAD_Org_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_org_id"])); alloclne.SetC_AllocationHdr_ID(allocationHeader); alloclne.SetC_BPartner_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_bpartner_id"])); alloclne.SetC_Invoice_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_invoice_id"])); alloclne.SetC_InvoicePaySchedule_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_invoicepayschedule_id"])); alloclne.SetDateTrx(System.DateTime.Now.ToLocalTime()); alloclne.SetAmount(Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["VA009_ConvertedAmt"])); alloclne.SetDiscountAmt(Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["discountamt"])); if (!alloclne.Save(trx)) { msg = Msg.GetMsg(ct, "VA009_PymentNotSaved"); ValueNamePair ppE = VAdvantage.Logging.VLogger.RetrieveError(); SavePaymentBachLog(Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_client_id"]), Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_org_id"]), recordID, ppE.ToString(), ct, trx); trx.Rollback(); payment.Clear(); viewAllocationId.Clear(); allocationDocumentNo = string.Empty; paymentDocumentNo = string.Empty; break; } else { // set Allocation ID on Batch Line Details batchLineDetails = new MVA009BatchLineDetails(ct, Util.GetValueOfInt(ds.Tables[0].Rows[i]["va009_batchlinedetails_ID"]), trx); batchLineDetails.SetC_AllocationHdr_ID(allocationHeader); batchLineDetails.Save(); } } else if (Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["VA009_DueAmount"]) == 0) { MAllocationHdr allocHdr = new MAllocationHdr(ct, 0, trx); allocHdr.SetAD_Client_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_client_id"])); allocHdr.SetAD_Org_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_org_id"])); allocHdr.SetDateAcct(System.DateTime.Now.ToLocalTime()); allocHdr.SetDateTrx(System.DateTime.Now.ToLocalTime()); allocHdr.SetC_Currency_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_currency_id"])); allocHdr.SetDocStatus("DR"); allocHdr.SetDocAction("CO"); if (!allocHdr.Save(trx)) { msg = Msg.GetMsg(ct, "VA009_PymentNotSaved"); ValueNamePair ppE = VAdvantage.Logging.VLogger.RetrieveError(); SavePaymentBachLog(Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_client_id"]), Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_org_id"]), recordID, ppE.ToString(), ct, trx); trx.Rollback(); payment.Clear(); viewAllocationId.Clear(); allocationDocumentNo = string.Empty; paymentDocumentNo = string.Empty; break; } else { if (!viewAllocationId.Contains(allocHdr.GetC_AllocationHdr_ID())) { viewAllocationId.Add(allocHdr.GetC_AllocationHdr_ID()); } MAllocationLine alloclne = new MAllocationLine(ct, 0, trx); alloclne.SetAD_Client_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_client_id"])); alloclne.SetAD_Org_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_org_id"])); alloclne.SetC_AllocationHdr_ID(allocHdr.GetC_AllocationHdr_ID()); alloclne.SetC_BPartner_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_bpartner_id"])); alloclne.SetC_Invoice_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_invoice_id"])); alloclne.SetC_InvoicePaySchedule_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_invoicepayschedule_id"])); alloclne.SetDateTrx(System.DateTime.Now.ToLocalTime()); alloclne.SetAmount(Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["VA009_ConvertedAmt"])); alloclne.SetDiscountAmt(Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["discountamt"])); if (!alloclne.Save(trx)) { msg = Msg.GetMsg(ct, "VA009_PymentNotSaved"); ValueNamePair ppE = VAdvantage.Logging.VLogger.RetrieveError(); SavePaymentBachLog(Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_client_id"]), Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_org_id"]), recordID, ppE.ToString(), ct, trx); trx.Rollback(); payment.Clear(); viewAllocationId.Clear(); allocationDocumentNo = string.Empty; paymentDocumentNo = string.Empty; break; } else { c_currency_id = Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_currency_id"]); Bpartner_ID = Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_bpartner_id"]); batchline_id = Util.GetValueOfInt(ds.Tables[0].Rows[i]["va009_batchlines_id"]); allocationHeader = allocHdr.GetC_AllocationHdr_ID(); allocationDocumentNo += allocHdr.GetDocumentNo() + " , "; // set Allocation ID on Batch Line //batchLines = new MVA009BatchLines(ct, Util.GetValueOfInt(ds.Tables[0].Rows[i]["va009_batchlines_id"]), trx.trx); //batchLineDetails.SetC_AllocationHdr_ID(allocationHeader); //batchLines.Save(); // set Allocation ID on Batch Line Details batchLineDetails = new MVA009BatchLineDetails(ct, Util.GetValueOfInt(ds.Tables[0].Rows[i]["va009_batchlinedetails_ID"]), trx); batchLineDetails.SetC_AllocationHdr_ID(allocationHeader); batchLineDetails.Save(); } } } #endregion #region Create a new entry of payment Allocate against same payment and the condition else if (c_currency_id == Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_currency_id"]) && Bpartner_ID == Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_bpartner_id"]) && batchline_id == Util.GetValueOfInt(ds.Tables[0].Rows[i]["va009_batchlines_id"])) { MPaymentAllocate PayAlocate = new MPaymentAllocate(ct, 0, trx); PayAlocate.SetC_Payment_ID(C_Payment_ID); PayAlocate.SetC_Invoice_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_invoice_id"])); PayAlocate.SetC_InvoicePaySchedule_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_invoicepayschedule_id"])); //if (Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]) == "ARC" || Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]) == "APC") //{ // PayAlocate.SetDiscountAmt(-1 * Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["discountamt"])); // PayAlocate.SetAmount(-1 * Util.GetValueOfInt(ds.Tables[0].Rows[i]["dueamt"])); // PayAlocate.SetInvoiceAmt(-1 * Util.GetValueOfInt(ds.Tables[0].Rows[i]["dueamt"])); //} //else //{ PayAlocate.SetDiscountAmt(Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["discountamt"])); PayAlocate.SetAmount(Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["VA009_ConvertedAmt"])); PayAlocate.SetInvoiceAmt(Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["dueamt"])); //} PayAlocate.SetAD_Client_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_client_id"])); PayAlocate.SetAD_Org_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_org_id"])); PayAlocate.SetWriteOffAmt(0); PayAlocate.SetOverUnderAmt(0); if (!PayAlocate.Save()) { msg = Msg.GetMsg(ct, "VA009_PymentAllocateNotSaved"); ValueNamePair ppE = VAdvantage.Logging.VLogger.RetrieveError(); SavePaymentBachLog(Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_client_id"]), Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_org_id"]), recordID, ppE.ToString(), ct, trx); trx.Rollback(); payment.Clear(); viewAllocationId.Clear(); allocationDocumentNo = string.Empty; paymentDocumentNo = string.Empty; break; } else { batchLineDetails = new MVA009BatchLineDetails(ct, Util.GetValueOfInt(ds.Tables[0].Rows[i]["va009_batchlinedetails_ID"]), trx); batchLineDetails.SetC_Payment_ID(C_Payment_ID); batchLineDetails.Save(); } } #endregion #region Create a new payment else { MPayment _pay = new MPayment(ct, 0, trx); int C_Doctype_ID = GetDocumnetType(Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]), ct); _pay.SetC_DocType_ID(C_Doctype_ID); _pay.SetAD_Client_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_client_id"])); _pay.SetAD_Org_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_org_id"])); _pay.SetDateAcct(System.DateTime.Now); _pay.SetDateTrx(System.DateTime.Now); _pay.SetC_BankAccount_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_bankaccount_id"])); _pay.SetC_BPartner_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_bpartner_id"])); _pay.SetC_Currency_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_currency_id"])); _pay.SetC_ConversionType_ID(CurrencyType_ID); _pay.SetVA009_PaymentMethod_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["va009_paymentmethod_id"])); tenderType = Util.GetValueOfString(DB.ExecuteScalar(@"select VA009_PAYMENTBASETYPE from VA009_PAYMENTMETHOD where VA009_PAYMENTMETHOD_ID=" + Util.GetValueOfInt(ds.Tables[0].Rows[i]["va009_paymentmethod_id"]))); if (tenderType == "K") // Credit Card { _pay.SetTenderType("C"); } else if (tenderType == "D") // Direct Debit { _pay.SetTenderType("D"); } else if (tenderType == "S") // Check { _pay.SetTenderType("K"); } else if (tenderType == "T") // Direct Deposit { _pay.SetTenderType("A"); } else { _pay.SetTenderType("A"); } if (!_pay.Save()) { msg = Msg.GetMsg(ct, "VA009_PymentNotSaved"); ValueNamePair ppE = VAdvantage.Logging.VLogger.RetrieveError(); SavePaymentBachLog(Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_client_id"]), Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_org_id"]), recordID, ppE.ToString(), ct, trx); trx.Rollback(); payment.Clear(); viewAllocationId.Clear(); allocationDocumentNo = string.Empty; paymentDocumentNo = string.Empty; break; } else { if (!payment.Contains(_pay.GetC_Payment_ID())) { payment.Add(_pay.GetC_Payment_ID()); } c_currency_id = Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_currency_id"]); Bpartner_ID = Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_bpartner_id"]); C_Payment_ID = _pay.GetC_Payment_ID(); batchline_id = Util.GetValueOfInt(ds.Tables[0].Rows[i]["va009_batchlines_id"]); paymentDocumentNo += _pay.GetDocumentNo() + " , "; MPaymentAllocate PayAlocate = new MPaymentAllocate(ct, 0, trx); PayAlocate.SetC_Payment_ID(C_Payment_ID); PayAlocate.SetC_Invoice_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_invoice_id"])); PayAlocate.SetC_InvoicePaySchedule_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_invoicepayschedule_id"])); //if (Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]) == "ARC" || Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]) == "APC") //{ // PayAlocate.SetDiscountAmt(-1 * Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["discountamt"])); // PayAlocate.SetAmount(-1 * Util.GetValueOfInt(ds.Tables[0].Rows[i]["dueamt"])); // PayAlocate.SetInvoiceAmt(-1 * Util.GetValueOfInt(ds.Tables[0].Rows[i]["dueamt"])); //} //else //{ PayAlocate.SetDiscountAmt(Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["discountamt"])); PayAlocate.SetAmount(Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["VA009_ConvertedAmt"])); PayAlocate.SetInvoiceAmt(Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["dueamt"])); //} PayAlocate.SetAD_Client_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_client_id"])); PayAlocate.SetAD_Org_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_org_id"])); PayAlocate.SetWriteOffAmt(0); PayAlocate.SetOverUnderAmt(0); if (!PayAlocate.Save()) { msg = Msg.GetMsg(ct, "VA009_PymentAllocateNotSaved"); ValueNamePair ppE = VAdvantage.Logging.VLogger.RetrieveError(); SavePaymentBachLog(Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_client_id"]), Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_org_id"]), recordID, ppE.ToString(), ct, trx); trx.Rollback(); payment.Clear(); viewAllocationId.Clear(); allocationDocumentNo = string.Empty; paymentDocumentNo = string.Empty; break; } else { batchLineDetails = new MVA009BatchLineDetails(ct, Util.GetValueOfInt(ds.Tables[0].Rows[i]["va009_batchlinedetails_ID"]), trx); batchLineDetails.SetC_Payment_ID(_pay.GetC_Payment_ID()); batchLineDetails.Save(); batchLines = new MVA009BatchLines(ct, Util.GetValueOfInt(ds.Tables[0].Rows[i]["va009_batchlines_id"]), trx); batchLines.SetC_Payment_ID(_pay.GetC_Payment_ID()); batchLines.Save(); } } } #endregion } // Complete the Consolidate Records of payment for (int i = 0; i < payment.Count(); i++) { MPayment completePayment = new MPayment(ct, payment[i], trx); if (completePayment.CompleteIt() == "CO") { completePayment.SetDocStatus("CO"); completePayment.SetDocAction("CL"); completePayment.Save(); } } // Complete the Consolidate Records of View allocation for (int i = 0; i < viewAllocationId.Count(); i++) { MAllocationHdr completeAllocation = new MAllocationHdr(ct, viewAllocationId[i], trx); if (completeAllocation.CompleteIt() == "CO") { completeAllocation.SetDocStatus("CO"); completeAllocation.SetDocAction("CL"); completeAllocation.Save(); } } } #endregion #region Consolidate = false else if (_batch.IsVA009_Consolidate() == false) { for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { MPayment _pay = new MPayment(ct, 0, trx); int C_Doctype_ID = GetDocumnetType(Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]), ct); _pay.SetC_DocType_ID(C_Doctype_ID); _pay.SetC_Invoice_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_invoice_id"])); _pay.SetC_InvoicePaySchedule_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_invoicepayschedule_id"])); _pay.SetAD_Client_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_client_id"])); _pay.SetAD_Org_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_org_id"])); _pay.SetDateAcct(System.DateTime.Now); _pay.SetDateTrx(System.DateTime.Now); _pay.SetC_ConversionType_ID(CurrencyType_ID); _pay.SetC_BankAccount_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_bankaccount_id"])); _pay.SetC_BPartner_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_bpartner_id"])); //if (Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]) == "ARC" || Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]) == "APC") //{ // _pay.SetDiscountAmt(-1 * Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["discountamt"])); // _pay.SetPayAmt(-1 * Util.GetValueOfInt(ds.Tables[0].Rows[i]["dueamt"])); //} //else //{ _pay.SetDiscountAmt(Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["discountamt"])); //_pay.SetPayAmt(Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["dueamt"])); _pay.SetPayAmt(Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["VA009_ConvertedAmt"])); //} _pay.SetC_Currency_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_currency_id"])); _pay.SetVA009_PaymentMethod_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["va009_paymentmethod_id"])); tenderType = Util.GetValueOfString(DB.ExecuteScalar(@"select VA009_PAYMENTBASETYPE from VA009_PAYMENTMETHOD where VA009_PAYMENTMETHOD_ID=" + Util.GetValueOfInt(ds.Tables[0].Rows[i]["va009_paymentmethod_id"]))); if (tenderType == "K") // Credit Card { _pay.SetTenderType("C"); } else if (tenderType == "D") // Direct Debit { _pay.SetTenderType("D"); } else if (tenderType == "S") // Check { _pay.SetTenderType("K"); } else if (tenderType == "T") // Direct Deposit { _pay.SetTenderType("A"); } else { _pay.SetTenderType("A"); } if (!_pay.Save(trx)) { msg = Msg.GetMsg(ct, "VA009_PymentNotSaved"); ValueNamePair ppE = VAdvantage.Logging.VLogger.RetrieveError(); SavePaymentBachLog(Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_client_id"]), Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_org_id"]), recordID, ppE.ToString(), ct, trx); trx.Rollback(); allocationDocumentNo = string.Empty; paymentDocumentNo = string.Empty; break; } else { paymentDocumentNo += _pay.GetDocumentNo() + " , "; batchLineDetails = new MVA009BatchLineDetails(ct, Util.GetValueOfInt(ds.Tables[0].Rows[i]["va009_batchlinedetails_ID"]), trx); batchLineDetails.SetC_Payment_ID(_pay.GetC_Payment_ID()); batchLineDetails.Save(trx); if (_pay.CompleteIt() == "CO") { _pay.SetDocStatus("CO"); _pay.SetDocAction("CL"); _pay.Save(trx); } } } } #endregion } else { return(msg = Msg.GetMsg(ct, "VA009_LinesNotAvailable")); } if (paymentDocumentNo != "" || allocationDocumentNo != "") { SaveRecordPaymentBachLog(_batch.GetAD_Client_ID(), _batch.GetAD_Org_ID(), recordID, paymentDocumentNo, allocationDocumentNo, ct, trx); } return(msg); }
/// <summary> /// Used to import XLSX,CSV,XLS files and Set values on Bank Statement line according to imported file. /// </summary> /// <param name="ctx"></param> /// <param name="FileName"></param> /// <param name="_path"></param> /// <param name="_bankaccount"></param> /// <param name="_bankAccountCurrency"></param> /// <param name="_statementno"></param> /// <param name="_statementCharges"></param> /// <returns>StatementResponse Object</returns> public VA012.Models.StatementResponse ImportStatement(Ctx ctx, string FileName, string _path, int _bankaccount, int _bankAccountCurrency, string _statementno, string _statementCharges) { VA012.Models.StatementResponse _obj = new VA012.Models.StatementResponse(); string _branchName = ""; string _IBAN = ""; //string abc = ""; //string _datasetvalues = ""; try { CultureInfo cultureInfo = CultureInfo.CurrentCulture; _log.Log(Level.INFO, " Culture Name --> " + cultureInfo.Name); #region Period StartDate and End Date DateTime?_startdate = null; DateTime?_enddate = null; //Get Start Date and End Date (Finacial Year start and end date) from Login Client (Client Info tab where calender ID is located) string _sqlDate = @"SELECT STARTDATE FROM C_PERIOD WHERE C_YEAR_ID = (SELECT (Y.C_YEAR_ID) AS C_YEAR_ID FROM C_YEAR Y INNER JOIN C_PERIOD P ON P.C_YEAR_ID = Y.C_YEAR_ID WHERE Y.C_CALENDAR_ID = (SELECT C_CALENDAR_ID FROM AD_CLIENTINFO WHERE AD_CLIENT_ID=" + ctx.GetAD_Client_ID() + @" ) AND TRUNC(SYSDATE) BETWEEN P.STARTDATE AND P.ENDDATE AND P.ISACTIVE = 'Y' AND Y.ISACTIVE ='Y' ) AND PERIODNO=1"; _startdate = Util.GetValueOfDateTime(DB.ExecuteScalar(_sqlDate)); _sqlDate = @"SELECT ENDDATE FROM C_PERIOD WHERE C_YEAR_ID = (SELECT (Y.C_YEAR_ID) AS C_YEAR_ID FROM C_YEAR Y INNER JOIN C_PERIOD P ON P.C_YEAR_ID = Y.C_YEAR_ID WHERE Y.C_CALENDAR_ID = (SELECT C_CALENDAR_ID FROM AD_CLIENTINFO WHERE AD_CLIENT_ID=" + ctx.GetAD_Client_ID() + @" ) AND TRUNC(SYSDATE) BETWEEN P.STARTDATE AND P.ENDDATE AND P.ISACTIVE = 'Y' AND Y.ISACTIVE ='Y' ) AND PERIODNO=12"; _enddate = Util.GetValueOfDateTime(DB.ExecuteScalar(_sqlDate)); #endregion int _existingStatementID = 0; string _statementDocStatus = ""; int pageno = 1; int lineno = 10; DataSet _ds = new DataSet(); _ds = DB.ExecuteDataset("SELECT C_BANKSTATEMENT_ID,DOCSTATUS FROM C_BANKSTATEMENT WHERE ISACTIVE='Y' AND NAME='" + _statementno + "' AND STATEMENTDATE BETWEEN " + GlobalVariable.TO_DATE(_startdate, true) + " AND " + GlobalVariable.TO_DATE(_enddate, true), null); if (_ds != null) { if (_ds.Tables[0].Rows.Count > 0) { _existingStatementID = Util.GetValueOfInt(_ds.Tables[0].Rows[0]["C_BANKSTATEMENT_ID"]); _statementDocStatus = Util.GetValueOfString(_ds.Tables[0].Rows[0]["DOCSTATUS"]); if (_statementDocStatus == "CO") { _obj._error = "VA012_StatementAlreadyExist"; return(_obj); } #region Get Page And Line string _sql = @"SELECT MAX(BSL.VA012_PAGE) AS PAGE FROM C_BANKSTATEMENTLINE BSL INNER JOIN C_BANKSTATEMENT BS ON BSL.C_BANKSTATEMENT_ID=BS.C_BANKSTATEMENT_ID WHERE BS.C_BANKSTATEMENT_ID =" + _existingStatementID; pageno = Util.GetValueOfInt(DB.ExecuteScalar(_sql)); if (pageno <= 0) { pageno = 1; } _sql = @"SELECT MAX(BSL.LINE)+10 AS LINE FROM C_BANKSTATEMENTLINE BSL INNER JOIN C_BANKSTATEMENT BS ON BSL.C_BANKSTATEMENT_ID=BS.C_BANKSTATEMENT_ID WHERE BS.C_BANKSTATEMENT_ID =" + _existingStatementID + " AND BSL.VA012_PAGE='" + pageno + "'"; lineno = Util.GetValueOfInt(DB.ExecuteScalar(_sql)); if (lineno <= 0) { lineno = 10; } #endregion } } _AD_Org_ID = Util.GetValueOfInt(ctx.GetAD_Org_ID()); _C_BankAccount_ID = _bankaccount; _AD_Org_ID = Util.GetValueOfInt(DB.ExecuteScalar("SELECT AD_Org_ID FROM C_BankAccount WHERE C_BankAccount_ID=" + _C_BankAccount_ID)); string _accountType = Util.GetValueOfString(DB.ExecuteScalar("Select BankAccountType from C_BankAccount Where C_BankAccount_ID=" + _C_BankAccount_ID)); int _stementID = 0; _Filenames.Append(FileName + ","); if (_Filenames.ToString() != "") { _Filenames.Remove(_Filenames.Length - 1, 1); } else { _obj._error = "VA012_AttachmentsAllreadyInSystem"; return(_obj); } _message = _Filenames.ToString(); string[] _filenamesall = _message.Split(','); for (int K = 0; K < _filenamesall.Length; K++) { _FileLocation = _filenamesall[K].ToString(); string[] _FileNameExten = _FileLocation.Split('.'); _FileName = _FileNameExten[0].ToString(); _Extension = "." + _FileNameExten[1].ToString(); //Import file if ((_Extension.ToUpper() == ".CSV") || (_Extension.ToUpper() == ".XLSX") || (_Extension.ToUpper() == ".XLS")) { DataSet ds = VA012.Models.ExcelImport.ImportFromCSV(_path, false, 1); if (File.Exists(_path)) { FileInfo fileToDelete = new FileInfo(_path); } if (ds != null) { DataTable dt = ds.Tables[0]; string _date = ""; string[] _dateList = new string[3]; if (dt.Rows.Count > 0) { #region ENBD Format string accountCurrency = Util.GetValueOfString(dt.Rows[2][0]); if (accountCurrency != null && accountCurrency != "") { accountCurrency = accountCurrency.Substring(accountCurrency.IndexOf(':') + 1).Trim(); } _C_Currency_ID = Util.GetValueOfInt(DB.ExecuteScalar("SELECT C_CURRENCY_ID FROM C_CURRENCY WHERE LOWER(DESCRIPTION) LIKE LOWER('" + accountCurrency + "') OR LOWER(ISO_CODE) LIKE LOWER('" + accountCurrency + "')")); if (_C_Currency_ID != _bankAccountCurrency) { _obj._error = "VA012_DiffAccountAndStatementCurrency"; return(_obj); } _branchName = ""; _IBAN = ""; for (int i = 0; i < dt.Rows.Count; i++) { if (i <= 2) { if (i == 1) { _IBAN = Util.GetValueOfString(dt.Rows[i][0]); _IBAN = _IBAN.Substring(_IBAN.LastIndexOf(':') + 1).Trim(); } continue; } #region Header if (i == 3) { if (_existingStatementID <= 0) { _BnkStatm = new MBankStatement(Env.GetCtx(), 0, null); _BnkStatm.SetAD_Client_ID(ctx.GetAD_Client_ID()); _BnkStatm.SetAD_Org_ID(_AD_Org_ID); _BnkStatm.SetC_BankAccount_ID(_C_BankAccount_ID); _BnkStatm.SetName(_statementno); _BnkStatm.SetStatementDate(DateTime.Now); if (!_BnkStatm.Save()) { //Used ValueNamePair to get error ValueNamePair pp = VLogger.RetrieveError(); //some times getting the error pp also string error = pp != null?pp.ToString() == null?pp.GetValue() : pp.ToString() : ""; if (string.IsNullOrEmpty(error)) { error = pp != null?pp.GetName() : ""; } _obj._error = !string.IsNullOrEmpty(error) ? error : "VA012_BankStatementHeaderNotSaved"; return(_obj); } else { _stementID = _BnkStatm.Get_ID(); } } else { _BnkStatm = new MBankStatement(Env.GetCtx(), _existingStatementID, null); } } #endregion #region Rest All Other Entries Which Contains Data else { _date = ""; if ((Util.GetValueOfString(dt.Rows[i][0]) != string.Empty) && (Util.GetValueOfString(dt.Rows[i][1]) != string.Empty)) { bool isDiffCulture = false; if (dt.Rows[i][0].ToString().Contains('.')) { isDiffCulture = true; _dateList = dt.Rows[i][0].ToString().Split('.'); if (_dateList.Length == 3) { _date = _dateList[1].ToString() + "/" + _dateList[0].ToString() + "/" + _dateList[2].ToString();// MM/DD/YYYY } } else { _date = dt.Rows[i][0].ToString(); } _BnkStmtLine = new MBankStatementLine(_BnkStatm); _BnkStmtLine.SetAD_Client_ID(ctx.GetAD_Client_ID()); _BnkStmtLine.SetAD_Org_ID(_AD_Org_ID); _BnkStmtLine.SetVA012_Page(pageno); _BnkStmtLine.SetLine(lineno); lineno = lineno + 10; _BnkStmtLine.SetStatementLineDate(DateTime.Parse(_date)); // Set Transaction Date _BnkStmtLine.SetDateAcct(DateTime.Parse(_date)); // Set Transaction Date _BnkStmtLine.SetValutaDate(DateTime.Parse(_date)); // Set Transaction Date _BnkStmtLine.SetReferenceNo(_IBAN); // Set Transaction Remarks _BnkStmtLine.SetDescription(Util.GetValueOfString(dt.Rows[i][1])); // Set Transaction Purticular _BnkStmtLine.SetMemo(_branchName); // Set Deposite Branch if (Util.GetValueOfString(dt.Rows[i][2]) != "") { _BnkStmtLine.SetEftCheckNo(Util.GetValueOfString(dt.Rows[i][2])); } if (Util.GetValueOfString(dt.Rows[i][6]) != "") { _c_bpartner_id = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT C_BPartner_ID FROM C_BPartner WHERE AD_Client_ID = " + ctx.GetAD_Client_ID() + @" AND ( LOWER(Value)= LOWER(" + GetConvertedValue(Util.GetValueOfString(dt.Rows[i][6])) + @") OR LOWER(Name)= LOWER(" + GetConvertedValue(Util.GetValueOfString(dt.Rows[i][6])) + "))")); _BnkStmtLine.SetC_BPartner_ID(_c_bpartner_id); } //set charge id if charge value is available in Bank Statement 7 column if (Util.GetValueOfString(dt.Rows[i][7]) != "") { _C_Charge_ID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT C_Charge_ID FROM C_Charge WHERE AD_Client_ID IN (0, " + ctx.GetAD_Client_ID() + @") AND LOWER(Value)= LOWER(" + GetConvertedValue(Util.GetValueOfString(dt.Rows[i][7])) + ")")); _BnkStmtLine.SetC_Charge_ID(_C_Charge_ID); //If chanrge id is available then set charge amount and statement amount on bank statement line suggested by Ashish. if (_C_Currency_ID > 0) { _BnkStmtLine.SetC_Currency_ID(_C_Currency_ID);// Set Currency Type } if ((Util.GetValueOfString(dt.Rows[i][3]) != string.Empty) && (Util.GetValueOfString(dt.Rows[i][3]) != "0")) { _payAmt = GetAmount(dt.Rows[i][3].ToString(), isDiffCulture); } else { _payAmt = GetAmount(dt.Rows[i][4].ToString(), isDiffCulture); } if ((Util.GetValueOfString(dt.Rows[i][3]) != string.Empty) && (Util.GetValueOfString(dt.Rows[i][3]) != "0")) { if (_payAmt != 0) { _BnkStmtLine.SetStmtAmt(Util.GetValueOfDecimal("-" + _payAmt)); _BnkStmtLine.SetChargeAmt(Util.GetValueOfDecimal("-" + _payAmt)); _BnkStmtLine.SetTrxAmt(Util.GetValueOfDecimal(0)); } else { _BnkStmtLine.SetStmtAmt(0); _BnkStmtLine.SetChargeAmt(0); _BnkStmtLine.SetTrxAmt(Util.GetValueOfDecimal(0)); } } else { _BnkStmtLine.SetStmtAmt(_payAmt); _BnkStmtLine.SetChargeAmt(_payAmt); _BnkStmtLine.SetTrxAmt(Util.GetValueOfDecimal(0)); } } else { if (_C_Currency_ID > 0) { _BnkStmtLine.SetC_Currency_ID(_C_Currency_ID);// Set Currency Type } if ((Util.GetValueOfString(dt.Rows[i][3]) != string.Empty) && (Util.GetValueOfString(dt.Rows[i][3]) != "0")) { _payAmt = GetAmount(dt.Rows[i][3].ToString(), isDiffCulture); } else { _payAmt = GetAmount(dt.Rows[i][4].ToString(), isDiffCulture); } if ((Util.GetValueOfString(dt.Rows[i][3]) != string.Empty) && (Util.GetValueOfString(dt.Rows[i][3]) != "0")) { if (_payAmt != 0) { _BnkStmtLine.SetStmtAmt(Util.GetValueOfDecimal("-" + _payAmt)); _BnkStmtLine.SetTrxAmt(Util.GetValueOfDecimal("-" + _payAmt)); } else { _BnkStmtLine.SetStmtAmt(0); _BnkStmtLine.SetTrxAmt(0); } } else { _BnkStmtLine.SetStmtAmt(_payAmt); _BnkStmtLine.SetTrxAmt(_payAmt); } } //Set TrxNo Value if exists in Excel sheet //changed ColumnName to ColumnIndex to avoid the Exception while fetching data from Excel if (!string.IsNullOrEmpty(Util.GetValueOfString(dt.Rows[i][8]))) { _BnkStmtLine.Set_Value("TrxNo", Util.GetValueOfString(dt.Rows[i][8])); } if (!_BnkStmtLine.Save()) { //Used ValueNamePair to get error ValueNamePair pp = VLogger.RetrieveError(); //some times getting the error pp also string error = pp != null?pp.ToString() == null?pp.GetValue() : pp.ToString() : ""; if (string.IsNullOrEmpty(error)) { error = pp != null?pp.GetName() : ""; } _obj._error = !string.IsNullOrEmpty(error) ? error : "VA012_StatementLineNotSaved"; return(_obj); } } else { } } #endregion } #endregion ENBD Format } else { _obj._error = "VA012_NoRecordsInExcel"; return(_obj); } } else { _obj._error = "VA012_NoRecordsInExcel"; return(_obj); } } else { _obj._error = "VA012_FormatNotSupported"; return(_obj); } } _obj._statementID = _stementID.ToString(); return(_obj); } catch (Exception e) { _obj._error = "VA012_ErrorInFileFormat"; return(_obj); } }