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);
}
}