public void TestMementoBank()
{
var ba = new MBankAccount(100);
// var m1 = ba.Deposit(50);
// var m2 = ba.Deposit(25);
// Console.WriteLine(ba);
// ba.Restore(m1);
// Console.WriteLine(ba);
// ba.Restore(m2);
// Console.WriteLine(ba);
ba.Deposit(50);
ba.Deposit(25);
Console.WriteLine(ba);
ba.Undo();
Console.WriteLine($"Undo 1: {ba}");
ba.Undo();
Console.WriteLine($"Undo 2: {ba}");
ba.Redo();
Console.WriteLine($"Redo: {ba}");
}
/// <summary>
/// Get Bank Currency
/// </summary> Arpit to Get Bank Currency
/// <param name="ctx"></param>
/// <param name="fields"></param>
/// <returns></returns>
public Dictionary <String, object> GetBankCurrency(Ctx ctx, string fields)
{
if (fields != null)
{
try
{
Dictionary <String, object> retDic = null;
string[] paramValue = fields.ToString().Split(',');
//Assign parameter value
int BankAccount_ID = Util.GetValueOfInt(paramValue[0].ToString());
//End Assign parameter
MBankAccount BankAcct = new MBankAccount(ctx, BankAccount_ID, null);
if (BankAcct != null)
{
retDic = new Dictionary <string, object>();
retDic["C_Currency_ID"] = BankAcct.GetC_Currency_ID();
return(retDic);
}
else
{
return(null);
}
}
catch (Exception ex)
{
String msg_ = ex.Message;
return(null);
}
}
else
{
return(null);
}
}
/**
* Get Bank Account
* @return bank account
*/
public MBankAccount GetBankAccount()
{
if (_bankAccount == null && GetC_BankAccount_ID() != 0)
{
_bankAccount = MBankAccount.Get(GetCtx(), GetC_BankAccount_ID());
}
return(_bankAccount);
}
/// <summary>
/// Get CurrentBalance
/// </summary>
/// <param name="ctx"></param>
/// <param name="fields"></param>
/// <returns></returns>
public Decimal GetCurrentBalance(Ctx ctx, string fields)
{
string[] paramValue = fields.Split(',');
//Assign parameter value
int C_BankAccount_ID = Util.GetValueOfInt(paramValue[0].ToString());
//End Assign parameter value
MBankAccount ba = MBankAccount.Get(ctx, C_BankAccount_ID);
return(ba.GetCurrentBalance());
}
/// <summary>
/// Get AD_Org_ID from Bank Account
/// </summary>
/// <returns>AD_Org_ID or 0</returns>
private int GetBank_Org_ID()
{
if (_C_BankAccount_ID == 0)
{
return(0);
}
//
MBankAccount ba = MBankAccount.Get(GetCtx(), _C_BankAccount_ID);
return(ba.GetAD_Org_ID());
}
/// <summary>
/// Load Specific Document Details
/// </summary>
/// <returns>error message or null</returns>
public override String LoadDocumentDetails()
{
MBankStatement bs = (MBankStatement)GetPO();
SetDateDoc(bs.GetStatementDate());
SetDateAcct(bs.GetStatementDate()); // Overwritten on Line Level
_C_BankAccount_ID = bs.GetC_BankAccount_ID();
// Amounts
SetAmount(AMTTYPE_Gross, bs.GetStatementDifference());
// Set Bank Account Info (Currency)
MBankAccount ba = MBankAccount.Get(GetCtx(), _C_BankAccount_ID);
SetC_Currency_ID(ba.GetC_Currency_ID());
// Contained Objects
_lines = LoadLines(bs);
log.Fine("Lines=" + _lines.Length);
return(null);
}
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"));
};
}
} // prepare
/// <summary>
/// doit
/// </summary>
/// <returns>Info</returns>
protected override String DoIt()
{
log.Info("C_BankAccount_ID" + _C_BankAccount_ID);
MBankAccount ba = MBankAccount.Get(GetCtx(), _C_BankAccount_ID);
if (_C_BankAccount_ID == 0 || ba.Get_ID() != _C_BankAccount_ID)
{
throw new Exception("@NotFound@ @C_BankAccount_ID@ - " + _C_BankAccount_ID);
}
if (_AD_Org_ID != ba.GetAD_Org_ID() && ba.GetAD_Org_ID() != 0)
{
_AD_Org_ID = ba.GetAD_Org_ID();
}
log.Info("AD_Org_ID=" + _AD_Org_ID);
StringBuilder sql = null;
int no = 0;
String clientCheck = " AND AD_Client_ID=" + ba.GetAD_Client_ID();
// **** Prepare ****
// Delete Old Imported
if (_deleteOldImported)
{
sql = new StringBuilder("DELETE FROM I_Payment "
+ "WHERE I_IsImported='Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Delete Old Impored =" + no);
}
// Set Client, Org, IsActive, Created/Updated
sql = new StringBuilder("UPDATE I_Payment "
+ "SET AD_Client_ID = COALESCE (AD_Client_ID,").Append(ba.GetAD_Client_ID()).Append("),"
+ " AD_Org_ID = COALESCE (AD_Org_ID,").Append(_AD_Org_ID).Append("),");
sql.Append(" IsActive = COALESCE (IsActive, 'Y'),"
+ " Created = COALESCE (Created, SysDate),"
+ " CreatedBy = COALESCE (CreatedBy, 0),"
+ " Updated = COALESCE (Updated, SysDate),"
+ " UpdatedBy = COALESCE (UpdatedBy, 0),"
+ " I_ErrorMsg = NULL,"
+ " I_IsImported = 'N' "
+ "WHERE I_IsImported<>'Y' OR I_IsImported IS NULL OR AD_Client_ID IS NULL OR AD_Org_ID IS NULL OR AD_Client_ID=0 OR AD_Org_ID=0");
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Info("Reset=" + no);
String ts = DataBase.DB.IsPostgreSQL() ? "COALESCE(I_ErrorMsg,'')" : "I_ErrorMsg"; //java bug, it could not be used directly
sql = new StringBuilder("UPDATE I_Payment o "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=Invalid Org, '"
+ "WHERE (AD_Org_ID IS NULL OR AD_Org_ID=0"
+ " OR EXISTS (SELECT * FROM AD_Org oo WHERE o.AD_Org_ID=oo.AD_Org_ID AND (oo.IsSummary='Y' OR oo.IsActive='N')))"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Warning("Invalid Org=" + no);
}
// Set Bank Account
sql = new StringBuilder("UPDATE I_Payment i "
+ "SET C_BankAccount_ID="
+ "( "
+ " SELECT C_BankAccount_ID "
+ " FROM C_BankAccount a, C_Bank b "
+ " WHERE b.IsOwnBank='Y' "
+ " AND a.AD_Client_ID=i.AD_Client_ID "
+ " AND a.C_Bank_ID=b.C_Bank_ID "
+ " AND a.AccountNo=i.BankAccountNo "
+ " AND b.RoutingNo=i.RoutingNo "
+ " OR b.SwiftCode=i.RoutingNo "
+ ") "
+ "WHERE i.C_BankAccount_ID IS NULL "
+ "AND i.I_IsImported<>'Y' "
+ "OR i.I_IsImported IS NULL").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Info("Bank Account (With Routing No)=" + no);
}
//
sql = new StringBuilder("UPDATE I_Payment i "
+ "SET C_BankAccount_ID="
+ "( "
+ " SELECT C_BankAccount_ID "
+ " FROM C_BankAccount a, C_Bank b "
+ " WHERE b.IsOwnBank='Y' "
+ " AND a.C_Bank_ID=b.C_Bank_ID "
+ " AND a.AccountNo=i.BankAccountNo "
+ " AND a.AD_Client_ID=i.AD_Client_ID "
+ ") "
+ "WHERE i.C_BankAccount_ID IS NULL "
+ "AND i.I_isImported<>'Y' "
+ "OR i.I_isImported IS NULL").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Info("Bank Account (Without Routing No)=" + no);
}
//
sql = new StringBuilder("UPDATE I_Payment i "
+ "SET C_BankAccount_ID=(SELECT C_BankAccount_ID FROM C_BankAccount a WHERE a.C_BankAccount_ID=").Append(_C_BankAccount_ID);
sql.Append(" and a.AD_Client_ID=i.AD_Client_ID) "
+ "WHERE i.C_BankAccount_ID IS NULL "
+ "AND i.BankAccountNo IS NULL "
+ "AND i.I_isImported<>'Y' "
+ "OR i.I_isImported IS NULL").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Info("Bank Account=" + no);
}
//
sql = new StringBuilder("UPDATE I_Payment "
+ "SET I_isImported='E', I_ErrorMsg=" + ts + "||'ERR=Invalid Bank Account, ' "
+ "WHERE C_BankAccount_ID IS NULL "
+ "AND I_isImported<>'Y' "
+ "OR I_isImported IS NULL").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Warning("Invalid Bank Account=" + no);
}
// Set Currency
sql = new StringBuilder("UPDATE I_Payment i "
+ "SET C_Currency_ID=(SELECT C_Currency_ID FROM C_Currency c"
+ " WHERE i.ISO_Code=c.ISO_Code AND c.AD_Client_ID IN (0,i.AD_Client_ID)) "
+ "WHERE C_Currency_ID IS NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Info("Set Currency=" + no);
}
//
sql = new StringBuilder("UPDATE I_Payment i "
//jz + "SET i.C_Currency_ID=(SELECT C_Currency_ID FROM C_BankAccount WHERE C_BankAccount_ID=i.C_BankAccount_ID) "
+ "SET C_Currency_ID=(SELECT C_Currency_ID FROM C_BankAccount WHERE C_BankAccount_ID=i.C_BankAccount_ID) "
+ "WHERE i.C_Currency_ID IS NULL "
+ "AND i.ISO_Code IS NULL").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Info("Set Currency=" + no);
}
//
sql = new StringBuilder("UPDATE I_Payment "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=No Currency,' "
+ "WHERE C_Currency_ID IS NULL "
+ "AND I_IsImported<>'E' "
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Warning("No Currency=" + no);
}
// Set Amount
sql = new StringBuilder("UPDATE I_Payment "
+ "SET ChargeAmt=0 "
+ "WHERE ChargeAmt IS NULL "
+ "AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Info("Charge Amount=" + no);
}
//
sql = new StringBuilder("UPDATE I_Payment "
+ "SET TaxAmt=0 "
+ "WHERE TaxAmt IS NULL "
+ "AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Info("Tax Amount=" + no);
}
//
sql = new StringBuilder("UPDATE I_Payment "
+ "SET WriteOffAmt=0 "
+ "WHERE WriteOffAmt IS NULL "
+ "AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Info("WriteOff Amount=" + no);
}
//
sql = new StringBuilder("UPDATE I_Payment "
+ "SET DiscountAmt=0 "
+ "WHERE DiscountAmt IS NULL "
+ "AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Info("Discount Amount=" + no);
}
//
// Set Date
sql = new StringBuilder("UPDATE I_Payment "
+ "SET DateTrx=Created "
+ "WHERE DateTrx IS NULL "
+ "AND I_isImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Info("Trx Date=" + no);
}
sql = new StringBuilder("UPDATE I_Payment "
+ "SET DateAcct=DateTrx "
+ "WHERE DateAcct IS NULL "
+ "AND I_isImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Info("Acct Date=" + no);
}
// Invoice
sql = new StringBuilder("UPDATE I_Payment i "
+ "SET C_Invoice_ID=(SELECT MAX(C_Invoice_ID) FROM C_Invoice ii"
+ " WHERE i.InvoiceDocumentNo=ii.DocumentNo AND i.AD_Client_ID=ii.AD_Client_ID) "
+ "WHERE C_Invoice_ID IS NULL AND InvoiceDocumentNo IS NOT NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Fine("Set Invoice from DocumentNo=" + no);
}
// BPartner
sql = new StringBuilder("UPDATE I_Payment i "
+ "SET C_BPartner_ID=(SELECT MAX(C_BPartner_ID) FROM C_BPartner bp"
+ " WHERE i.BPartnerValue=bp.Value AND i.AD_Client_ID=bp.AD_Client_ID) "
+ "WHERE C_BPartner_ID IS NULL AND BPartnerValue IS NOT NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Fine("Set BP from Value=" + no);
}
sql = new StringBuilder("UPDATE I_Payment i "
+ "SET C_BPartner_ID=(SELECT MAX(C_BPartner_ID) FROM C_Invoice ii"
+ " WHERE i.C_Invoice_ID=ii.C_Invoice_ID AND i.AD_Client_ID=ii.AD_Client_ID) "
+ "WHERE C_BPartner_ID IS NULL AND C_Invoice_ID IS NOT NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Fine("Set BP from Invoice=" + no);
}
sql = new StringBuilder("UPDATE I_Payment "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=No BPartner,' "
+ "WHERE C_BPartner_ID IS NULL "
+ "AND I_IsImported<>'E' "
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Warning("No BPartner=" + no);
}
// Check Payment<->Invoice combination
sql = new StringBuilder("UPDATE I_Payment "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'Err=Invalid Payment<->Invoice, ' "
+ "WHERE I_Payment_ID IN "
+ "(SELECT I_Payment_ID "
+ "FROM I_Payment i"
+ " INNER JOIN C_Payment p ON (i.C_Payment_ID=p.C_Payment_ID) "
+ "WHERE i.C_Invoice_ID IS NOT NULL "
+ " AND p.C_Invoice_ID IS NOT NULL "
+ " AND p.C_Invoice_ID<>i.C_Invoice_ID) ")
.Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Info("Payment<->Invoice Mismatch=" + no);
}
// Check Payment<->BPartner combination
sql = new StringBuilder("UPDATE I_Payment "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'Err=Invalid Payment<->BPartner, ' "
+ "WHERE I_Payment_ID IN "
+ "(SELECT I_Payment_ID "
+ "FROM I_Payment i"
+ " INNER JOIN C_Payment p ON (i.C_Payment_ID=p.C_Payment_ID) "
+ "WHERE i.C_BPartner_ID IS NOT NULL "
+ " AND p.C_BPartner_ID IS NOT NULL "
+ " AND p.C_BPartner_ID<>i.C_BPartner_ID) ")
.Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Info("Payment<->BPartner Mismatch=" + no);
}
// Check Invoice<->BPartner combination
sql = new StringBuilder("UPDATE I_Payment "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'Err=Invalid Invoice<->BPartner, ' "
+ "WHERE I_Payment_ID IN "
+ "(SELECT I_Payment_ID "
+ "FROM I_Payment i"
+ " INNER JOIN C_Invoice v ON (i.C_Invoice_ID=v.C_Invoice_ID) "
+ "WHERE i.C_BPartner_ID IS NOT NULL "
+ " AND v.C_BPartner_ID IS NOT NULL "
+ " AND v.C_BPartner_ID<>i.C_BPartner_ID) ")
.Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Info("Invoice<->BPartner Mismatch=" + no);
}
// Check Invoice.BPartner<->Payment.BPartner combination
sql = new StringBuilder("UPDATE I_Payment "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'Err=Invalid Invoice.BPartner<->Payment.BPartner, ' "
+ "WHERE I_Payment_ID IN "
+ "(SELECT I_Payment_ID "
+ "FROM I_Payment i"
+ " INNER JOIN C_Invoice v ON (i.C_Invoice_ID=v.C_Invoice_ID)"
+ " INNER JOIN C_Payment p ON (i.C_Payment_ID=p.C_Payment_ID) "
+ "WHERE p.C_Invoice_ID<>v.C_Invoice_ID"
+ " AND v.C_BPartner_ID<>p.C_BPartner_ID) ")
.Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Info("Invoice.BPartner<->Payment.BPartner Mismatch=" + no);
}
// TrxType
sql = new StringBuilder("UPDATE I_Payment "
+ "SET TrxType='S' " // MPayment.TRXTYPE_Sales
+ "WHERE TrxType IS NULL "
+ "AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Info("TrxType Default=" + no);
}
// TenderType
sql = new StringBuilder("UPDATE I_Payment "
+ "SET TenderType='K' " // MPayment.TENDERTYPE_Check
+ "WHERE TenderType IS NULL "
+ "AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Info("TenderType Default=" + no);
}
// Document Type
sql = new StringBuilder("UPDATE I_Payment i "
+ "SET C_DocType_ID=(SELECT C_DocType_ID FROM C_DocType d WHERE d.Name=i.DocTypeName"
+ " AND d.DocBaseType IN ('ARR','APP') AND i.AD_Client_ID=d.AD_Client_ID) "
+ "WHERE C_DocType_ID IS NULL AND DocTypeName IS NOT NULL AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Fine("Set DocType=" + no);
}
sql = new StringBuilder("UPDATE I_Payment "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=Invalid DocTypeName, ' "
+ "WHERE C_DocType_ID IS NULL AND DocTypeName IS NOT NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Warning("Invalid DocTypeName=" + no);
}
sql = new StringBuilder("UPDATE I_Payment "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=No DocType, ' "
+ "WHERE C_DocType_ID IS NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Warning("No DocType=" + no);
}
Commit();
//Import Bank Statement
sql = new StringBuilder("SELECT * FROM I_Payment"
+ " WHERE I_IsImported='N'"
+ " ORDER BY C_BankAccount_ID, CheckNo, DateTrx, R_AuthCode");
MBankAccount account = null;
IDataReader idr = null;
// int lineNo = 10;
int noInsert = 0;
//int noInsertLine = 0;
try
{
//pstmt = DataBase.prepareStatement(sql.ToString(), get_TrxName());
//ResultSet rs = pstmt.executeQuery();
idr = DataBase.DB.ExecuteReader(sql.ToString(), null, Get_TrxName());
while (idr.Read())
{
X_I_Payment imp = new X_I_Payment(_ctx, idr, Get_TrxName());
// Get the bank account
if (account == null || account.GetC_BankAccount_ID() != imp.GetC_BankAccount_ID())
{
account = MBankAccount.Get(_ctx, imp.GetC_BankAccount_ID());
log.Info("New Account=" + account.GetAccountNo());
}
// New Payment
MPayment payment = new MPayment(_ctx, 0, Get_TrxName());
payment.SetAD_Org_ID(imp.GetAD_Org_ID());
payment.SetDocumentNo(imp.GetDocumentNo());
payment.SetPONum(imp.GetPONum());
payment.SetTrxType(imp.GetTrxType());
payment.SetTenderType(imp.GetTenderType());
payment.SetC_BankAccount_ID(imp.GetC_BankAccount_ID());
payment.SetRoutingNo(imp.GetRoutingNo());
payment.SetAccountNo(imp.GetAccountNo());
payment.SetCheckNo(imp.GetCheckNo());
payment.SetMicr(imp.GetMicr());
if (imp.GetCreditCardType() != null)
{
payment.SetCreditCardType(imp.GetCreditCardType());
}
payment.SetCreditCardNumber(imp.GetCreditCardNumber());
if (imp.GetCreditCardExpMM() != 0)
{
payment.SetCreditCardExpMM(imp.GetCreditCardExpMM());
}
if (imp.GetCreditCardExpYY() != 0)
{
payment.SetCreditCardExpYY(imp.GetCreditCardExpYY());
}
payment.SetCreditCardVV(imp.GetCreditCardVV());
payment.SetSwipe(imp.GetSwipe());
payment.SetDateAcct(imp.GetDateTrx());
payment.SetDateTrx(imp.GetDateTrx());
// payment.setDescription(imp.getDescription());
//
payment.SetC_BPartner_ID(imp.GetC_BPartner_ID());
payment.SetC_Invoice_ID(imp.GetC_Invoice_ID());
payment.SetC_DocType_ID(imp.GetC_DocType_ID());
payment.SetC_Currency_ID(imp.GetC_Currency_ID());
// payment.setC_ConversionType_ID(imp.getC_ConversionType_ID());
payment.SetC_Charge_ID(imp.GetC_Charge_ID());
payment.SetChargeAmt(imp.GetChargeAmt());
payment.SetTaxAmt(imp.GetTaxAmt());
payment.SetPayAmt(imp.GetPayAmt());
payment.SetWriteOffAmt(imp.GetWriteOffAmt());
payment.SetDiscountAmt(imp.GetDiscountAmt());
payment.SetWriteOffAmt(imp.GetWriteOffAmt());
// Copy statement line reference data
payment.SetA_City(imp.GetA_City());
payment.SetA_Country(imp.GetA_Country());
payment.SetA_EMail(imp.GetA_EMail());
payment.SetA_Ident_DL(imp.GetA_Ident_DL());
payment.SetA_Ident_SSN(imp.GetA_Ident_SSN());
payment.SetA_Name(imp.GetA_Name());
payment.SetA_State(imp.GetA_State());
payment.SetA_Street(imp.GetA_Street());
payment.SetA_Zip(imp.GetA_Zip());
payment.SetR_AuthCode(imp.GetR_AuthCode());
payment.SetR_Info(imp.GetR_Info());
payment.SetR_PnRef(imp.GetR_PnRef());
payment.SetR_RespMsg(imp.GetR_RespMsg());
payment.SetR_Result(imp.GetR_Result());
payment.SetOrig_TrxID(imp.GetOrig_TrxID());
payment.SetVoiceAuthCode(imp.GetVoiceAuthCode());
// Save patment
if (payment.Save())
{
imp.SetC_Payment_ID(payment.GetC_Payment_ID());
imp.SetI_IsImported(X_I_Payment.I_ISIMPORTED_Yes);
imp.SetProcessed(true);
imp.Save();
noInsert++;
}
}
// Close database connection
idr.Close();
}
catch (Exception e)
{
if (idr != null)
{
idr.Close();
}
log.Log(Level.SEVERE, sql.ToString(), e);
}
// Set Error to indicator to not imported
sql = new StringBuilder("UPDATE I_Payment "
+ "SET I_IsImported='N', Updated=SysDate "
+ "WHERE I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
AddLog(0, null, Utility.Util.GetValueOfDecimal(no), "@Errors@");
//
AddLog(0, null, Utility.Util.GetValueOfDecimal(noInsert), "@C_Payment_ID@: @Inserted@");
return("");
} // doIt
} // prepare
/// <summary>
/// perform process
/// </summary>
/// <returns>info</returns>
protected override String DoIt()
{
log.Info("AD_Org_ID=" + _AD_Org_ID + ", C_BankAccount_ID" + _C_BankAccount_ID);
StringBuilder sql = null;
int no = 0;
String clientCheck = " AND AD_Client_ID=" + _AD_Client_ID;
// **** Prepare ****
// Delete Old Imported
if (_deleteOldImported)
{
sql = new StringBuilder("DELETE FROM I_BankStatement "
+ "WHERE I_IsImported='Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Delete Old Impored =" + no);
}
// Set Client, Org, IsActive, Created/Updated
sql = new StringBuilder("UPDATE I_BankStatement "
+ "SET AD_Client_ID = COALESCE (AD_Client_ID,").Append(_AD_Client_ID).Append("),"
+ " AD_Org_ID = COALESCE (AD_Org_ID,").Append(_AD_Org_ID).Append("),");
sql.Append(" IsActive = COALESCE (IsActive, 'Y'),"
+ " Created = COALESCE (Created, SysDate),"
+ " CreatedBy = COALESCE (CreatedBy, 0),"
+ " Updated = COALESCE (Updated, SysDate),"
+ " UpdatedBy = COALESCE (UpdatedBy, 0),"
+ " I_ErrorMsg = NULL,"
+ " I_IsImported = 'N' "
+ "WHERE I_IsImported<>'Y' OR I_IsImported IS NULL OR AD_Client_ID IS NULL OR AD_Org_ID IS NULL OR AD_Client_ID=0 OR AD_Org_ID=0");
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Info("Reset=" + no);
String ts = DataBase.DB.IsPostgreSQL() ? "COALESCE(I_ErrorMsg,'')" : "I_ErrorMsg"; //java bug, it could not be used directly
sql = new StringBuilder("UPDATE I_BankStatement o "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=Invalid Org, '"
+ "WHERE (AD_Org_ID IS NULL OR AD_Org_ID=0"
+ " OR EXISTS (SELECT * FROM AD_Org oo WHERE o.AD_Org_ID=oo.AD_Org_ID AND (oo.IsSummary='Y' OR oo.IsActive='N')))"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Warning("Invalid Org=" + no);
}
// Set Bank Account
sql = new StringBuilder("UPDATE I_BankStatement i "
+ "SET C_BankAccount_ID="
+ "( "
+ " SELECT C_BankAccount_ID "
+ " FROM C_BankAccount a, C_Bank b "
+ " WHERE b.IsOwnBank='Y' "
+ " AND a.AD_Client_ID=i.AD_Client_ID "
+ " AND a.C_Bank_ID=b.C_Bank_ID "
+ " AND a.AccountNo=i.BankAccountNo "
+ " AND b.RoutingNo=i.RoutingNo "
+ " OR b.SwiftCode=i.RoutingNo "
+ ") "
+ "WHERE i.C_BankAccount_ID IS NULL "
+ "AND i.I_IsImported<>'Y' "
+ "OR i.I_IsImported IS NULL").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Info("Bank Account (With Routing No)=" + no);
}
//
sql = new StringBuilder("UPDATE I_BankStatement i "
+ "SET C_BankAccount_ID="
+ "( "
+ " SELECT C_BankAccount_ID "
+ " FROM C_BankAccount a, C_Bank b "
+ " WHERE b.IsOwnBank='Y' "
+ " AND a.C_Bank_ID=b.C_Bank_ID "
+ " AND a.AccountNo=i.BankAccountNo "
+ " AND a.AD_Client_ID=i.AD_Client_ID "
+ ") "
+ "WHERE i.C_BankAccount_ID IS NULL "
+ "AND i.I_isImported<>'Y' "
+ "OR i.I_isImported IS NULL").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Info("Bank Account (Without Routing No)=" + no);
}
//
sql = new StringBuilder("UPDATE I_BankStatement i "
+ "SET C_BankAccount_ID=(SELECT C_BankAccount_ID FROM C_BankAccount a WHERE a.C_BankAccount_ID=").Append(_C_BankAccount_ID);
sql.Append(" and a.AD_Client_ID=i.AD_Client_ID) "
+ "WHERE i.C_BankAccount_ID IS NULL "
+ "AND i.BankAccountNo IS NULL "
+ "AND i.I_isImported<>'Y' "
+ "OR i.I_isImported IS NULL").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Info("Bank Account=" + no);
}
//
sql = new StringBuilder("UPDATE I_BankStatement "
+ "SET I_isImported='E', I_ErrorMsg=" + ts + "||'ERR=Invalid Bank Account, ' "
+ "WHERE C_BankAccount_ID IS NULL "
+ "AND I_isImported<>'Y' "
+ "OR I_isImported IS NULL").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Warning("Invalid Bank Account=" + no);
}
// Set Currency
sql = new StringBuilder("UPDATE I_BankStatement i "
+ "SET C_Currency_ID=(SELECT C_Currency_ID FROM C_Currency c"
+ " WHERE i.ISO_Code=c.ISO_Code AND c.AD_Client_ID IN (0,i.AD_Client_ID)) "
+ "WHERE C_Currency_ID IS NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Info("Set Currency=" + no);
}
//
sql = new StringBuilder("UPDATE I_BankStatement i "
//jz + "SET i.C_Currency_ID=(SELECT C_Currency_ID FROM C_BankAccount WHERE C_BankAccount_ID=i.C_BankAccount_ID) "
+ "SET C_Currency_ID=(SELECT C_Currency_ID FROM C_BankAccount WHERE C_BankAccount_ID=i.C_BankAccount_ID) "
+ "WHERE i.C_Currency_ID IS NULL "
+ "AND i.ISO_Code IS NULL").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Info("Set Currency=" + no);
}
//
sql = new StringBuilder("UPDATE I_BankStatement "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=Invalid Currency,' "
+ "WHERE C_Currency_ID IS NULL "
+ "AND I_IsImported<>'E' "
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Warning("Invalid Currency=" + no);
}
// Set Amount
sql = new StringBuilder("UPDATE I_BankStatement "
+ "SET ChargeAmt=0 "
+ "WHERE ChargeAmt IS NULL "
+ "AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Info("Charge Amount=" + no);
}
//
sql = new StringBuilder("UPDATE I_BankStatement "
+ "SET InterestAmt=0 "
+ "WHERE InterestAmt IS NULL "
+ "AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Info("Interest Amount=" + no);
}
//
sql = new StringBuilder("UPDATE I_BankStatement "
+ "SET TrxAmt=StmtAmt - InterestAmt - ChargeAmt "
+ "WHERE TrxAmt IS NULL "
+ "AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Info("Transaction Amount=" + no);
}
//
sql = new StringBuilder("UPDATE I_BankStatement "
+ "SET I_isImported='E', I_ErrorMsg=" + ts + "||'Err=Invalid Amount, ' "
+ "WHERE TrxAmt + ChargeAmt + InterestAmt <> StmtAmt "
+ "AND I_isImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Info("Invaid Amount=" + no);
}
// Set Valuta Date
sql = new StringBuilder("UPDATE I_BankStatement "
+ "SET ValutaDate=StatementLineDate "
+ "WHERE ValutaDate IS NULL "
+ "AND I_isImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Info("Valuta Date=" + no);
}
// Check Payment<->Invoice combination
sql = new StringBuilder("UPDATE I_BankStatement "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'Err=Invalid Payment<->Invoice, ' "
+ "WHERE I_BankStatement_ID IN "
+ "(SELECT I_BankStatement_ID "
+ "FROM I_BankStatement i"
+ " INNER JOIN C_Payment p ON (i.C_Payment_ID=p.C_Payment_ID) "
+ "WHERE i.C_Invoice_ID IS NOT NULL "
+ " AND p.C_Invoice_ID IS NOT NULL "
+ " AND p.C_Invoice_ID<>i.C_Invoice_ID) ")
.Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Info("Payment<->Invoice Mismatch=" + no);
}
// Check Payment<->BPartner combination
sql = new StringBuilder("UPDATE I_BankStatement "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'Err=Invalid Payment<->BPartner, ' "
+ "WHERE I_BankStatement_ID IN "
+ "(SELECT I_BankStatement_ID "
+ "FROM I_BankStatement i"
+ " INNER JOIN C_Payment p ON (i.C_Payment_ID=p.C_Payment_ID) "
+ "WHERE i.C_BPartner_ID IS NOT NULL "
+ " AND p.C_BPartner_ID IS NOT NULL "
+ " AND p.C_BPartner_ID<>i.C_BPartner_ID) ")
.Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Info("Payment<->BPartner Mismatch=" + no);
}
// Check Invoice<->BPartner combination
sql = new StringBuilder("UPDATE I_BankStatement "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'Err=Invalid Invoice<->BPartner, ' "
+ "WHERE I_BankStatement_ID IN "
+ "(SELECT I_BankStatement_ID "
+ "FROM I_BankStatement i"
+ " INNER JOIN C_Invoice v ON (i.C_Invoice_ID=v.C_Invoice_ID) "
+ "WHERE i.C_BPartner_ID IS NOT NULL "
+ " AND v.C_BPartner_ID IS NOT NULL "
+ " AND v.C_BPartner_ID<>i.C_BPartner_ID) ")
.Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Info("Invoice<->BPartner Mismatch=" + no);
}
// Check Invoice.BPartner<->Payment.BPartner combination
sql = new StringBuilder("UPDATE I_BankStatement "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'Err=Invalid Invoice.BPartner<->Payment.BPartner, ' "
+ "WHERE I_BankStatement_ID IN "
+ "(SELECT I_BankStatement_ID "
+ "FROM I_BankStatement i"
+ " INNER JOIN C_Invoice v ON (i.C_Invoice_ID=v.C_Invoice_ID)"
+ " INNER JOIN C_Payment p ON (i.C_Payment_ID=p.C_Payment_ID) "
+ "WHERE p.C_Invoice_ID<>v.C_Invoice_ID"
+ " AND v.C_BPartner_ID<>p.C_BPartner_ID) ")
.Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Info("Invoice.BPartner<->Payment.BPartner Mismatch=" + no);
}
// Detect Duplicates
sql = new StringBuilder("SELECT i.I_BankStatement_ID, l.C_BankStatementLine_ID, i.EftTrxID "
+ "FROM I_BankStatement i, C_BankStatement s, C_BankStatementLine l "
+ "WHERE i.I_isImported='N' "
+ "AND s.C_BankStatement_ID=l.C_BankStatement_ID "
+ "AND i.EftTrxID IS NOT NULL AND "
// Concatinate EFT Info
+ "(l.EftTrxID||l.EftAmt||l.EftStatementLineDate||l.EftValutaDate||l.EftTrxType||l.EftCurrency||l.EftReference||s.EftStatementReference "
+ "||l.EftCheckNo||l.EftMemo||l.EftPayee||l.EftPayeeAccount) "
+ "= "
+ "(i.EftTrxID||i.EftAmt||i.EftStatementLineDate||i.EftValutaDate||i.EftTrxType||i.EftCurrency||i.EftReference||i.EftStatementReference "
+ "||i.EftCheckNo||i.EftMemo||i.EftPayee||i.EftPayeeAccount) ");
StringBuilder updateSql = new StringBuilder("UPDATE I_Bankstatement "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'Err=Duplicate['||@param1||']' "
+ "WHERE I_BankStatement_ID=@param2").Append(clientCheck);
//PreparedStatement pupdt = DataBase.prepareStatement(updateSql.ToString(), get_TrxName());
//PreparedStatement pstmtDuplicates = null;
IDataReader idr = null;
SqlParameter[] param = new SqlParameter[2];
no = 0;
try
{
//pstmtDuplicates = DataBase.prepareStatement(sql.ToString(), get_TrxName());
//ResultSet rs = pstmtDuplicates.executeQuery();
idr = DataBase.DB.ExecuteReader(sql.ToString(), null, Get_TrxName());
while (idr.Read())
{
String Info = "Line_ID=" + Utility.Util.GetValueOfInt(idr[1]) // s.getInt(2) // l.C_BankStatementLine_ID
+ ",EDTTrxID=" + Utility.Util.GetValueOfString(idr[2]); // rs.getString(3); // i.EftTrxID
//pupdt.setString(1, Info);
param[0] = new SqlParameter("@param1", Info);
//pupdt.setInt(2, rs.getInt(1)); // i.I_BankStatement_ID
param[1] = new SqlParameter("@param2", Utility.Util.GetValueOfInt(idr[0]));
//pupdt.executeUpdate();
DataBase.DB.ExecuteQuery(updateSql.ToString(), param, Get_TrxName());
no++;
}
idr.Close();
}
catch (Exception e)
{
if (idr != null)
{
idr.Close();
}
log.Log(Level.SEVERE, "DetectDuplicates " + e.Message);
}
if (no != 0)
{
log.Info("Duplicates=" + no);
}
Commit();
//Import Bank Statement
sql = new StringBuilder("SELECT * FROM I_BankStatement"
+ " WHERE I_IsImported='N'"
+ " ORDER BY C_BankAccount_ID, Name, EftStatementDate, EftStatementReference");
MBankStatement statement = null;
MBankAccount account = null;
//PreparedStatement pstmt = null;
int lineNo = 10;
int noInsert = 0;
int noInsertLine = 0;
try
{
//pstmt = DataBase.prepareStatement(sql.ToString(), get_TrxName());
idr = DataBase.DB.ExecuteReader(sql.ToString(), null, Get_TrxName());
while (idr.Read())
{
X_I_BankStatement imp = new X_I_BankStatement(_ctx, idr, Get_TrxName());
// Get the bank account for the first statement
if (account == null)
{
account = MBankAccount.Get(_ctx, imp.GetC_BankAccount_ID());
statement = null;
log.Info("New Statement, Account=" + account.GetAccountNo());
}
// Create a new Bank Statement for every account
else if (account.GetC_BankAccount_ID() != imp.GetC_BankAccount_ID())
{
account = MBankAccount.Get(_ctx, imp.GetC_BankAccount_ID());
statement = null;
log.Info("New Statement, Account=" + account.GetAccountNo());
}
// Create a new Bank Statement for every statement name
else if ((statement.GetName() != null) && (imp.GetName() != null))
{
if (!statement.GetName().Equals(imp.GetName()))
{
statement = null;
log.Info("New Statement, Statement Name=" + imp.GetName());
}
}
// Create a new Bank Statement for every statement reference
else if ((statement.GetEftStatementReference() != null) && (imp.GetEftStatementReference() != null))
{
if (!statement.GetEftStatementReference().Equals(imp.GetEftStatementReference()))
{
statement = null;
log.Info("New Statement, Statement Reference=" + imp.GetEftStatementReference());
}
}
// Create a new Bank Statement for every statement date
else if ((statement.GetStatementDate() != null) && (imp.GetStatementDate() != null))
{
if (!statement.GetStatementDate().Equals(imp.GetStatementDate()))
{
statement = null;
log.Info("New Statement, Statement Date=" + imp.GetStatementDate());
}
}
// New Statement
if (statement == null)
{
statement = new MBankStatement(account);
statement.SetEndingBalance(Env.ZERO);
// Copy statement data
if (imp.GetName() != null)
{
statement.SetName(imp.GetName());
}
if (imp.GetStatementDate() != null)
{
statement.SetStatementDate(imp.GetStatementDate());
}
statement.SetDescription(imp.GetDescription());
statement.SetEftStatementReference(imp.GetEftStatementReference());
statement.SetEftStatementDate(imp.GetEftStatementDate());
if (statement.Save())
{
noInsert++;
}
lineNo = 10;
}
// New StatementLine
MBankStatementLine line = new MBankStatementLine(statement, lineNo);
// Copy statement line data
//line.setC_BPartner_ID(imp.getC_BPartner_ID());
//line.setC_Invoice_ID(imp.getC_Invoice_ID());
line.SetReferenceNo(imp.GetReferenceNo());
line.SetDescription(imp.GetLineDescription());
line.SetStatementLineDate(imp.GetStatementLineDate());
line.SetDateAcct(imp.GetStatementLineDate());
line.SetValutaDate(imp.GetValutaDate());
line.SetIsReversal(imp.IsReversal());
line.SetC_Currency_ID(imp.GetC_Currency_ID());
line.SetTrxAmt(imp.GetTrxAmt());
line.SetStmtAmt(imp.GetStmtAmt());
if (imp.GetC_Charge_ID() != 0)
{
line.SetC_Charge_ID(imp.GetC_Charge_ID());
}
line.SetInterestAmt(imp.GetInterestAmt());
line.SetChargeAmt(imp.GetChargeAmt());
line.SetMemo(imp.GetMemo());
if (imp.GetC_Payment_ID() != 0)
{
line.SetC_Payment_ID(imp.GetC_Payment_ID());
}
// Copy statement line reference data
line.SetEftTrxID(imp.GetEftTrxID());
line.SetEftTrxType(imp.GetEftTrxType());
line.SetEftCheckNo(imp.GetEftCheckNo());
line.SetEftReference(imp.GetEftReference());
line.SetEftMemo(imp.GetEftMemo());
line.SetEftPayee(imp.GetEftPayee());
line.SetEftPayeeAccount(imp.GetEftPayeeAccount());
line.SetEftStatementLineDate(imp.GetEftStatementLineDate());
line.SetEftValutaDate(imp.GetEftValutaDate());
line.SetEftCurrency(imp.GetEftCurrency());
line.SetEftAmt(imp.GetEftAmt());
// Save statement line
if (line.Save())
{
imp.SetC_BankStatement_ID(statement.GetC_BankStatement_ID());
imp.SetC_BankStatementLine_ID(line.GetC_BankStatementLine_ID());
imp.SetI_IsImported(X_I_BankStatement.I_ISIMPORTED_Yes);
imp.SetProcessed(true);
imp.Save();
noInsertLine++;
lineNo += 10;
}
line = null;
}
// Close database connection
idr.Close();
}
catch (Exception e)
{
if (idr != null)
{
idr.Close();
}
log.Log(Level.SEVERE, sql.ToString(), e);
}
// Set Error to indicator to not imported
sql = new StringBuilder("UPDATE I_BankStatement "
+ "SET I_IsImported='N', Updated=SysDate "
+ "WHERE I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
AddLog(0, null, Utility.Util.GetValueOfDecimal(no), "@Errors@");
//
AddLog(0, null, Utility.Util.GetValueOfDecimal(noInsert), "@C_BankStatement_ID@: @Inserted@");
AddLog(0, null, Utility.Util.GetValueOfDecimal(noInsertLine), "@C_BankStatementLine_ID@: @Inserted@");
return("");
} // doIt
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"));
}
}