/// <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());
//
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());
if (payment == null)
{
throw new SystemException("Could not create Payment");
}
// update statement
bsl.SetPayment(payment);
bsl.Save();
//
String retString = "@C_Payment_ID@ = " + payment.GetDocumentNo();
if (Env.Signum(payment.GetOverUnderAmt()) != 0)
{
retString += " - @OverUnderAmt@=" + payment.GetOverUnderAmt();
}
return(retString);
}
/// <summary>
/// Load Invoice Line.
/// 4 amounts
/// AMTTYPE_Payment
/// AMTTYPE_Statement2
/// AMTTYPE_Charge
/// AMTTYPE_Interest
/// </summary>
/// <param name="bs">bank statement</param>
/// <returns>DocLine Array</returns>
private DocLine[] LoadLines(MBankStatement bs)
{
List <DocLine> list = new List <DocLine>();
MBankStatementLine[] lines = bs.GetLines(false);
for (int i = 0; i < lines.Length; i++)
{
MBankStatementLine line = lines[i];
DocLine_Bank docLine = new DocLine_Bank(line, this);
// Set Date Acct
if (i == 0)
{
SetDateAcct(line.GetDateAcct());
}
MPeriod period = MPeriod.Get(GetCtx(), line.GetDateAcct());
if (period != null && period.IsOpen(MDocBaseType.DOCBASETYPE_BANKSTATEMENT))
{
docLine.SetC_Period_ID(period.GetC_Period_ID());
}
//
list.Add(docLine);
}
// Return Array
DocLine[] dls = new DocLine[list.Count];
dls = list.ToArray();
return(dls);
}
/// <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);
}
/// <summary>
/// Perform Match
/// </summary>
/// <param name="bs">bank statement</param>
/// <returns>Message</returns>
private String Match(MBankStatement bs)
{
if (_matchers == null || bs == null)
{
return("--");
}
log.Fine("match - " + bs);
int count = 0;
MBankStatementLine[] lines = bs.GetLines(false);
for (int i = 0; i < lines.Length; i++)
{
if (lines[i].GetC_Payment_ID() == 0)
{
Match(lines[i]);
count++;
}
}
//return String.valueOf(count);
return(count.ToString());
}
public StatementResponse ImportStatement(Ctx ctx, string FileName, string _path, int _bankaccount, int _bankAccountCurrency, string _statementno, string _statementCharges)
{
StatementResponse _obj = new StatementResponse();
try
{
_C_Currency_ID = Convert.ToInt32(DB.ExecuteScalar("Select C_Currency_ID from C_Currency Where iso_code= 'INR'"));
if (_C_Currency_ID != _bankAccountCurrency)
{
_obj._error = "VA012_DiffAccountAndStatementCurrency";
return(_obj);
}
#region Period StartDate and End Date
DateTime?_startdate = null;
DateTime?_enddate = null;
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;
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();
//if ((_Extension.ToUpper() == ".CSV") || (_Extension.ToUpper() == ".XLSX") || (_Extension.ToUpper() == ".XLS") || (_Extension.ToUpper() == ".TXT"))
if (_Extension.ToUpper() == ".TXT")
{
DataSet ds = ExcelImport.ImportFromCSV(_path, false);
if (File.Exists(_path))
{
FileInfo fileToDelete = new FileInfo(_path);
fileToDelete.Delete();
}
if (ds != null)
{
DataTable dt = ds.Tables[0];
StringBuilder _qry = new StringBuilder();
Boolean _inward = true;
int _bpID = 0;
_qry.Clear();
if (dt.Rows.Count > 0)
{
//for (int i = 0; i < dt.Rows.Count - 2; i++)
for (int i = 0; i < dt.Rows.Count; i++)
{
if (i == 0)
{
#region Statement Header
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(System.DateTime.Now);
// _BnkStatm.SetBeginningBalance(Convert.ToDecimal(dt.Rows[i][9]));
if (!_BnkStatm.Save())
{
_obj._error = "VA012_BankStatementHeaderNotSaved";
return(_obj);
}
else
{
_stementID = _BnkStatm.Get_ID();
}
}
else
{
_BnkStatm = new MBankStatement(Env.GetCtx(), _existingStatementID, null);
}
#endregion
}
#region Statement Line
_BnkStmtLine = new MBankStatementLine(_BnkStatm);
#region Check Inward Or Outward
_qry.Clear();
_qry.Append(@"SELECT COUNT(VA017_INWARDPAYMENTPREFIX) AS INWARD
FROM VA017_ICICI_PAYMENT
WHERE ISACTIVE ='Y'
AND C_BANKACCOUNT_ID =" + _C_BankAccount_ID
+ @" AND TRIM(UPPER(VA017_INWARDPAYMENTPREFIX))=TRIM(UPPER('" + Convert.ToString(dt.Rows[i][0]) + "'))");
if (Util.GetValueOfInt(DB.ExecuteScalar(_qry.ToString())) > 0)
{
_inward = true;
}
else
{
_qry.Clear();
_qry.Append(@"SELECT COUNT(VA017_OUTWARDPAYMENTPREFIX) AS OUTWARD
FROM VA017_ICICI_PAYMENT
WHERE ISACTIVE ='Y'
AND C_BANKACCOUNT_ID =" + _C_BankAccount_ID
+ @" AND TRIM(UPPER(VA017_OUTWARDPAYMENTPREFIX))=TRIM(UPPER('" + Convert.ToString(dt.Rows[i][0]) + "'))");
if (Util.GetValueOfInt(DB.ExecuteScalar(_qry.ToString())) > 0)
{
_inward = false;
}
else
{
_obj._error = "VA012_SetInwardOutwardPrefix";
return(_obj);
}
}
#endregion Check Inward Or Outward
_BnkStmtLine.SetAD_Client_ID(ctx.GetAD_Client_ID());
_BnkStmtLine.SetAD_Org_ID(ctx.GetAD_Org_ID());
_BnkStmtLine.SetVA012_Page(pageno);
_BnkStmtLine.SetLine(lineno);
lineno = lineno + 10;
_BnkStmtLine.SetReferenceNo(Convert.ToString(dt.Rows[i][1]));
////
_qry.Clear();
_qry.Append("SELECT C_BPARTNER_ID FROM C_BPARTNER WHERE AD_Client_ID = " + ctx.GetAD_Client_ID() + " AND C_BPARTNER_ID=" + Util.GetValueOfInt(dt.Rows[i][1]));
_bpID = Util.GetValueOfInt(DB.ExecuteScalar(_qry.ToString()));
if (_bpID > 0)
{
_BnkStmtLine.SetC_BPartner_ID(_bpID);
}
////
if (_inward)
{
_BnkStmtLine.SetStmtAmt(Math.Abs(Convert.ToDecimal(dt.Rows[i][2])));
_BnkStmtLine.SetTrxAmt(Math.Abs(Convert.ToDecimal(dt.Rows[i][2])));
}
else
{
_BnkStmtLine.SetStmtAmt(Decimal.Negate(Math.Abs(Convert.ToDecimal(dt.Rows[i][2]))));
_BnkStmtLine.SetTrxAmt(Decimal.Negate(Math.Abs(Convert.ToDecimal(dt.Rows[i][2]))));
}
_BnkStmtLine.SetStatementLineDate(GetDate(dt.Rows[i][3].ToString()));
_BnkStmtLine.SetDateAcct(GetDate(dt.Rows[i][3].ToString()));
_BnkStmtLine.SetEftTrxID(Convert.ToString(dt.Rows[i][4]));
_BnkStmtLine.SetEftReference(Convert.ToString(dt.Rows[i][5]));
_BnkStmtLine.SetEftPayeeAccount(Convert.ToString(dt.Rows[i][6]));
_BnkStmtLine.SetDescription(Convert.ToString(dt.Rows[i][7]));
_BnkStmtLine.SetEftMemo(Convert.ToString(dt.Rows[i][8]));
_BnkStmtLine.SetEftTrxType(Convert.ToString(dt.Rows[i][9]));
_BnkStmtLine.SetEftPayee(Convert.ToString(dt.Rows[i][10]));
if (_C_Currency_ID > 0)
{
_BnkStmtLine.SetC_Currency_ID(_C_Currency_ID);
}
if (dt.Columns.Count > 11)
{
_BnkStmtLine.SetEftCheckNo(Convert.ToString(dt.Rows[i][11]));
}
if (!_BnkStmtLine.Save())
{
}
#endregion
}
_BnkStatm.SetEndingBalance(_BnkStatm.GetBeginningBalance() + _BnkStatm.GetStatementDifference());
if (!_BnkStatm.Save())
{
_obj._error = "VA012_EndingBalanceNotUpdated";
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);
}
}
public StatementResponse ImportStatement(Ctx ctx, string FileName, string _path, int _bankaccount, int _bankAccountCurrency, string _statementno, string _statementCharges)
{
StatementResponse _obj = new StatementResponse();
#region Period StartDate and End Date
DateTime?_startdate = null;
DateTime?_enddate = null;
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;
string _accountType = Util.GetValueOfString(DB.ExecuteScalar("Select BankAccountType from C_BankAccount Where C_BankAccount_ID=" + _C_BankAccount_ID));
// SaveAttachment();
int _stementID = 0;
_Filenames.Append(FileName + ",");
// Number Of New Files Saved In Our System
if (_Filenames.ToString() != "")
{
_Filenames.Remove(_Filenames.Length - 1, 1);
}
else
{
_obj._error = "VA012_AttachmentsAllreadyInSystem";
return(_obj);
}
// New Files To Update In Our System
_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();
if ((_Extension.ToUpper() == ".CSV") || (_Extension.ToUpper() == ".XLSX") || (_Extension.ToUpper() == ".XLS"))
{
DataSet ds = ExcelImport.ImportFromCSV(_path, false);
if (File.Exists(_path))
{
FileInfo fileToDelete = new FileInfo(_path);
fileToDelete.Delete();
}
if (ds != null && ds.Tables.Count > 0)
{
#region [NEW FORMAT]
DataTable dt = ds.Tables[0];
if (dt.Rows.Count > 0)
{
try
{
if (!dt.Columns.Contains("MERCHANT_ID") ||
!dt.Columns.Contains("CHAIN_ID") ||
!dt.Columns.Contains("AUTHORIZATION_CODE"))
{
_obj._error = "VA012_ErrorInFileFormat";
return(_obj);
}
//int chargeID = Util.GetValueOfInt(DB.ExecuteScalar("SELECT C_Charge_ID FROM C_Charge WHERE DTD001_ChargeType = 'OTH' AND AD_CLIENT_ID =" + ctx.GetAD_Client_ID()));
int chargeID = Util.GetValueOfInt(_statementCharges);
for (int i = -1; i < dt.Rows.Count; i++)
{
#region Header
if (i == -1)
{
_C_Currency_ID = Convert.ToInt32(DB.ExecuteScalar("Select C_Currency_ID from C_Currency Where iso_code= '" + (dt.Rows[i + 1][8]).ToString().Trim() + "'"));
if (_C_Currency_ID != _bankAccountCurrency)
{
_obj._error = "VA012_DiffAccountAndStatementCurrency";
return(_obj);
}
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())
{
_obj._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
{
if (!string.IsNullOrEmpty(dt.Rows[i][1].ToString().Trim()) && !string.IsNullOrEmpty(dt.Rows[i][2].ToString().Trim())) //if it is not a sum of rows
{
_BnkStmtLine = new MBankStatementLine(_BnkStatm);
_BnkStmtLine.SetAD_Client_ID(ctx.GetAD_Client_ID());
_BnkStmtLine.SetAD_Org_ID(ctx.GetAD_Org_ID());
_BnkStmtLine.SetVA012_Page(pageno);
_BnkStmtLine.SetLine(lineno);
lineno = lineno + 10;
_BnkStmtLine.SetStatementLineDate(Convert.ToDateTime(DateTime.ParseExact(dt.Rows[i][9].ToString().Trim(), "dd/MM/yyyy", null))); // Set Transaction Date
_BnkStmtLine.SetDateAcct(Convert.ToDateTime(DateTime.ParseExact(dt.Rows[i][9].ToString().Trim(), "dd/MM/yyyy", null))); // Set Transaction Date
_BnkStmtLine.SetValutaDate(Convert.ToDateTime(DateTime.ParseExact(dt.Rows[i][9].ToString().Trim(), "dd/MM/yyyy", null))); // Set Transaction Date
_BnkStmtLine.SetReferenceNo(Convert.ToString(dt.Rows[i][19]).Trim()); // Set Reference No.
_BnkStmtLine.SetDescription(Convert.ToString(dt.Rows[i][19]).Trim()); // Set Reference No.
_BnkStmtLine.SetMemo(Convert.ToString(dt.Rows[i][3]).Trim() + " " + Convert.ToString(dt.Rows[i][4]).Trim()); // Set Merchant name and Location
// _C_Currency_ID = Convert.ToInt32(DB.ExecuteScalar("Select C_Currency_ID from C_Currency Where iso_code= '" + (dt.Rows[i][8].ToString().Trim()) + "'"));
if (_C_Currency_ID > 0)
{
_BnkStmtLine.SetC_Currency_ID(_C_Currency_ID);// Set Currency Type
}
if ((Convert.ToString(dt.Rows[i][17]).Trim() != string.Empty) && (Convert.ToString(dt.Rows[i][17]).Trim() != "0"))
{
_payAmt = Convert.ToDecimal(dt.Rows[i][17].ToString().Trim());
}
if ((Convert.ToString(dt.Rows[i][14]).Trim() != string.Empty) && (Convert.ToString(dt.Rows[i][14]).Trim() != "0"))
{
_trxAmt = Convert.ToDecimal(dt.Rows[i][14].ToString().Trim());
}
if (_accountType == "C")
{
if ((Convert.ToString(dt.Rows[i][14]).Trim() != string.Empty) && (Convert.ToString(dt.Rows[i][14]).Trim() != "0"))
{
_BnkStmtLine.SetStmtAmt(_payAmt);
_BnkStmtLine.SetTrxAmt(_trxAmt);
}
else
{
_BnkStmtLine.SetStmtAmt(Convert.ToDecimal("-" + _payAmt));
_BnkStmtLine.SetTrxAmt(Convert.ToDecimal("-" + _trxAmt));
}
}
else
{
if ((Convert.ToString(dt.Rows[i][14]).Trim() != string.Empty) && (Convert.ToString(dt.Rows[i][15]).Trim() != "0"))
{
_BnkStmtLine.SetStmtAmt(Convert.ToDecimal("-" + _payAmt));
_BnkStmtLine.SetTrxAmt(Convert.ToDecimal("-" + _trxAmt));
}
else
{
_BnkStmtLine.SetStmtAmt(_payAmt);
_BnkStmtLine.SetTrxAmt(_trxAmt);
}
}
//if charges present
if ((Convert.ToString(dt.Rows[i][15]).Trim() != string.Empty) && (Convert.ToString(dt.Rows[i][15]).Trim() != "0"))
{
_BnkStmtLine.SetChargeAmt(Convert.ToDecimal(dt.Rows[i][15].ToString().Trim()));
_BnkStmtLine.SetC_Charge_ID(chargeID);
var _sql = "SELECT C_TAX_ID FROM C_TAX WHERE AD_Client_ID = " + ctx.GetAD_Client_ID() + " AND EXPORT_ID IS NOT NULL AND ISDEFAULT = 'Y' AND IsActive='Y'";
// _sql = MRole.GetDefault(ctx).AddAccessSQL(_sql, "C_Tax", true, false);
int _C_Tax_ID = Convert.ToInt32(DB.ExecuteScalar(_sql));
if (_C_Tax_ID > 0)
{
_BnkStmtLine.SetC_Tax_ID(_C_Tax_ID);
var Rate = Util.GetValueOfDecimal(DB.ExecuteScalar("Select Rate From C_Tax Where C_Tax_ID=" + _C_Tax_ID + " AND IsActive='Y'"));
if (Rate > 0)
{
var TaxAmt = Math.Round(Util.GetValueOfDecimal(_BnkStmtLine.GetChargeAmt() - (_BnkStmtLine.GetChargeAmt() / ((Rate / 100) + 1))), 2);
_BnkStmtLine.SetTaxAmt(TaxAmt);
}
else
{
_BnkStmtLine.SetTaxAmt(0);
}
}
}
_BnkStmtLine.Set_Value("TrxNo", Convert.ToString(dt.Rows[i][12]).Trim());
if (!_BnkStmtLine.Save())
{
}
}
}
#endregion
_BnkStatm.SetEndingBalance(_BnkStatm.GetBeginningBalance() + _BnkStatm.GetStatementDifference());
if (!_BnkStatm.Save())
{
_obj._error = "VA012_BeginningBalanceNotUpdated";
return(_obj);
}
}
}
catch (Exception ex)
{
_obj._error = "VA012_ErrorInFileFormat";
return(_obj);
}
}
else
{
_obj._error = "VA012_NoRecordsInExcel";
return(_obj);
}
#endregion [NEW FORMAT]
}
else
{
_obj._error = "VA012_NoRecordsInExcel";
return(_obj);
}
}
else
{
_obj._error = "VA012_FormatNotSupported";
return(_obj);
}
}
_obj._statementID = _stementID.ToString();
return(_obj);
}
} // 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
/// <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 StatementResponse ImportStatement(Ctx ctx, string FileName, string _path, int _bankaccount, int _bankAccountCurrency, string _statementno, string _statementCharges, DateTime?statementDate)
{
StatementResponse _obj = new StatementResponse();
#region Period StartDate and End Date
DateTime?_startdate = null;
DateTime?_enddate = null;
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 TO_CHAR(STATEMENTDATE,'YYYY')=TO_CHAR(sysdate,'YYYY') ", null);
_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.NAME ='" + _statementno + "' AND BSL.VA012_PAGE='" + pageno + "' AND TO_CHAR(BS.STATEMENTDATE,'YYYY')=TO_CHAR(sysdate,'YYYY') ";
_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;
string _accountType = Util.GetValueOfString(DB.ExecuteScalar("Select BankAccountType from C_BankAccount Where C_BankAccount_ID=" + _C_BankAccount_ID));
// SaveAttachment();
int _stementID = 0;
_Filenames.Append(FileName + ",");
// Number Of New Files Saved In Our System
if (_Filenames.ToString() != "")
{
_Filenames.Remove(_Filenames.Length - 1, 1);
}
else
{
_obj._error = "VA012_AttachmentsAllreadyInSystem";
return(_obj);
}
// New Files To Update In Our System
_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();
// _FileNameExten = null;
//_FileNameExten = _FileName.Split('-');
// _date = _FileNameExten[1].ToString();
if ((_Extension.ToUpper() == ".CSV") || (_Extension.ToUpper() == ".XLSX") || (_Extension.ToUpper() == ".XLS"))
{
// DataSet ds = ImportFromCSV(HostingEnvironment.ApplicationPhysicalPath + @"\Attachment\" +_FileLocation, false);
DataSet ds = ExcelImport.ImportFromCSV(_path, false);
if (File.Exists(_path))
{
FileInfo fileToDelete = new FileInfo(_path);
fileToDelete.Delete();
//DirectoryInfo myDirInfo = new DirectoryInfo(filepath.Substring(0, filepath.LastIndexOf("\\")));
//foreach (FileInfo file in myDirInfo.GetFiles())
//{
// file.Delete();
//}
//Directory.Delete(filepath.Substring(0, filepath.LastIndexOf("\\")));
}
if (ds != null)
{
#region Pattern For ICICI Bank E-Statements
DataTable dt = ds.Tables[0];
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count - 2; i++)
{
//For 1 to 3 lines of CSV which contains Nothing
if (i <= 3)
{
continue;
}
#region For First Line Which Contains B/F (Balance Forward)
if (i == 4)
{
_C_Currency_ID = Convert.ToInt32(DB.ExecuteScalar("Select C_Currency_ID from C_Currency Where iso_code= '" + (dt.Rows[i][6]) + "'"));
if (_C_Currency_ID != _bankAccountCurrency)
{
_obj._error = "VA012_DiffAccountAndStatementCurrency";
return(_obj);
}
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(statementDate);
_BnkStatm.SetBeginningBalance(Convert.ToDecimal(dt.Rows[i][9]));
if (!_BnkStatm.Save())
{
_obj._error = "VA012_BankStatementHeaderNotSaved";
//_obj._error = "VA012_Header Not Saved Of Bank Statement";
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
{
// If Check Number Exists
if ((Convert.ToString(dt.Rows[i][0]) != string.Empty) && (Convert.ToString(dt.Rows[i][4]) != string.Empty) && (Convert.ToString(dt.Rows[i][6]) != string.Empty) && ((Convert.ToString(dt.Rows[i][7]) != string.Empty) || (Convert.ToString(dt.Rows[i][8]) != string.Empty)))
{
_BnkStmtLine = new MBankStatementLine(_BnkStatm);
_BnkStmtLine.SetAD_Client_ID(ctx.GetAD_Client_ID());
_BnkStmtLine.SetAD_Org_ID(ctx.GetAD_Org_ID());
_BnkStmtLine.SetVA012_Page(pageno);
_BnkStmtLine.SetLine(lineno);
lineno = lineno + 10;
_BnkStmtLine.SetStatementLineDate(Convert.ToDateTime(dt.Rows[i][1])); // Set Transaction Date
_BnkStmtLine.SetDateAcct(Convert.ToDateTime(dt.Rows[i][1])); // Set Transaction Date
_BnkStmtLine.SetValutaDate(Convert.ToDateTime(dt.Rows[i][1])); // Set Transaction Date
_BnkStmtLine.SetReferenceNo(Convert.ToString(dt.Rows[i][3])); // Set Transaction Remarks
_BnkStmtLine.SetDescription(Convert.ToString(dt.Rows[i][2])); // Set Transaction Purticular
_BnkStmtLine.SetEftCheckNo(Convert.ToString(dt.Rows[i][4])); // Set Check Number
_BnkStmtLine.SetMemo(Convert.ToString(dt.Rows[i][10])); // Set Deposite Branch
_C_Currency_ID = Convert.ToInt32(DB.ExecuteScalar("Select C_Currency_ID from C_Currency Where iso_code= '" + (dt.Rows[i][6]) + "'"));
if (_C_Currency_ID > 0)
{
_BnkStmtLine.SetC_Currency_ID(_C_Currency_ID);// Set Currency Type
}
if ((Convert.ToString(dt.Rows[i][7]) != string.Empty) && (Convert.ToString(dt.Rows[i][7]) != "0"))
{
_payAmt = Convert.ToDecimal(dt.Rows[i][7]);
}
else
{
_payAmt = Convert.ToDecimal(dt.Rows[i][8]);
}
if (_accountType == "C")
{
if ((Convert.ToString(dt.Rows[i][7]) != string.Empty) && (Convert.ToString(dt.Rows[i][7]) != "0"))
{
_BnkStmtLine.SetStmtAmt(_payAmt);
_BnkStmtLine.SetTrxAmt(_payAmt);
}
else
{
_BnkStmtLine.SetStmtAmt(Convert.ToDecimal("-" + _payAmt));
_BnkStmtLine.SetTrxAmt(Convert.ToDecimal("-" + _payAmt));
}
}
else
{
if ((Convert.ToString(dt.Rows[i][7]) != string.Empty) && (Convert.ToString(dt.Rows[i][7]) != "0"))
{
_BnkStmtLine.SetStmtAmt(Convert.ToDecimal("-" + _payAmt));
_BnkStmtLine.SetTrxAmt(Convert.ToDecimal("-" + _payAmt));
}
else
{
_BnkStmtLine.SetStmtAmt(_payAmt);
_BnkStmtLine.SetTrxAmt(_payAmt);
}
}
//PyDS = DB.ExecuteDataset("SELECT cp.c_payment_id as c_payment_id, cd.name as doctype,cp.c_invoice_id as c_invoice_id,cp.c_bpartner_id as c_bpartner_id FROM c_payment cp inner join c_doctype cd on cd.c_doctype_id= cp.c_doctype_id WHERE cp.c_bankaccount_id=" + _C_BankAccount_ID + " AND cp.c_currency_id = " + _C_Currency_ID + " AND cp.checkno ='" + Convert.ToString(dt.Rows[i][4]) + "' AND cp.payamt =" + _payAmt + "");
//if (PyDS != null)
//{
// if (PyDS.Tables[0].Rows.Count > 0)
// {
// _c_payment_id = Convert.ToInt32(PyDS.Tables[0].Rows[0]["c_payment_id"]);
// _doctype = Convert.ToString(PyDS.Tables[0].Rows[0]["doctype"]);
// _BnkStmtLine.SetC_BPartner_ID(Convert.ToInt32(PyDS.Tables[0].Rows[0]["c_bpartner_id"]));
// _BnkStmtLine.SetC_Invoice_ID(Convert.ToInt32(PyDS.Tables[0].Rows[0]["c_invoice_id"]));
// if (_c_payment_id != null && _c_payment_id != 0)
// {
// if (_doctype == "AP Payment")
// {
// _BnkStmtLine.SetC_Payment_ID(_c_payment_id);
// _BnkStmtLine.SetStmtAmt(Convert.ToDecimal("-" + _payAmt));
// _BnkStmtLine.SetTrxAmt(Convert.ToDecimal("-" + _payAmt));
// }
// else if (_doctype == "AR Receipt")
// {
// _BnkStmtLine.SetC_Payment_ID(_c_payment_id);
// _BnkStmtLine.SetStmtAmt(_payAmt);
// _BnkStmtLine.SetTrxAmt(_payAmt);
// }
// }
// }
// else
// {
// if ((Convert.ToString(dt.Rows[i][7]) != string.Empty) && (Convert.ToString(dt.Rows[i][7]) != "0"))
// {
// _BnkStmtLine.SetStmtAmt(Convert.ToDecimal("-" + _payAmt));
// _BnkStmtLine.SetTrxAmt(Convert.ToDecimal("-" + _payAmt));
// }
// else
// {
// _BnkStmtLine.SetStmtAmt(_payAmt);
// _BnkStmtLine.SetTrxAmt(_payAmt);
// }
// }
//}
if (!_BnkStmtLine.Save())
{
_obj._error = "VA012_StatementLineNotSaved";
//_obj._error = "VA012_Statement Line Not Saved";
return(_obj);
}
}
// If Check Number Doesn't Exists
else
{
_BnkStmtLine = new MBankStatementLine(_BnkStatm);
_BnkStmtLine.SetAD_Client_ID(ctx.GetAD_Client_ID());
_BnkStmtLine.SetAD_Org_ID(ctx.GetAD_Org_ID());
_BnkStmtLine.SetVA012_Page(pageno);
_BnkStmtLine.SetLine(lineno);
lineno = lineno + 10;
_BnkStmtLine.SetStatementLineDate(Convert.ToDateTime(dt.Rows[i][1])); // Set Transaction Date
_BnkStmtLine.SetDateAcct(Convert.ToDateTime(dt.Rows[i][1])); // Set Transaction Date
_BnkStmtLine.SetValutaDate(Convert.ToDateTime(dt.Rows[i][1])); // Set Transaction Date
_BnkStmtLine.SetReferenceNo(Convert.ToString(dt.Rows[i][3])); // Set Transaction Remarks
_BnkStmtLine.SetDescription(Convert.ToString(dt.Rows[i][2])); // Set Transaction Purticular
_BnkStmtLine.SetMemo(Convert.ToString(dt.Rows[i][10])); // Set Deposite Branch
_C_Currency_ID = Convert.ToInt32(DB.ExecuteScalar("Select C_Currency_ID from C_Currency Where iso_code= '" + (dt.Rows[i][6]) + "'"));
if (_C_Currency_ID > 0)
{
_BnkStmtLine.SetC_Currency_ID(_C_Currency_ID);// Set Currency Type
}
if ((Convert.ToString(dt.Rows[i][7]) != string.Empty) && (Convert.ToString(dt.Rows[i][7]) != "0"))
{
_payAmt = Convert.ToDecimal(dt.Rows[i][7]);
}
else
{
_payAmt = Convert.ToDecimal(dt.Rows[i][8]);
}
if (_accountType == "C")
{
if ((Convert.ToString(dt.Rows[i][7]) != string.Empty) && (Convert.ToString(dt.Rows[i][7]) != "0"))
{
_BnkStmtLine.SetStmtAmt(_payAmt);
_BnkStmtLine.SetTrxAmt(_payAmt);
}
else
{
_BnkStmtLine.SetStmtAmt(Convert.ToDecimal("-" + _payAmt));
_BnkStmtLine.SetTrxAmt(Convert.ToDecimal("-" + _payAmt));
}
}
else
{
if ((Convert.ToString(dt.Rows[i][7]) != string.Empty) && (Convert.ToString(dt.Rows[i][7]) != "0"))
{
_BnkStmtLine.SetStmtAmt(Convert.ToDecimal("-" + _payAmt));
_BnkStmtLine.SetTrxAmt(Convert.ToDecimal("-" + _payAmt));
}
else
{
_BnkStmtLine.SetStmtAmt(_payAmt);
_BnkStmtLine.SetTrxAmt(_payAmt);
}
}
//PyDS = DB.ExecuteDataset("SELECT cp.c_payment_id as c_payment_id, cd.name as doctype,cp.c_invoice_id as c_invoice_id,cp.c_bpartner_id as c_bpartner_id FROM c_payment cp inner join c_doctype cd on cd.c_doctype_id= cp.c_doctype_id WHERE cp.c_bankaccount_id=" + _C_BankAccount_ID + " AND cp.c_currency_id = " + _C_Currency_ID + " AND cp.payamt =" + _payAmt + "");
//if (PyDS != null)
//{
// if (PyDS.Tables[0].Rows.Count > 0)
// {
// _c_payment_id = Convert.ToInt32(PyDS.Tables[0].Rows[0]["c_payment_id"]);
// _doctype = Convert.ToString(PyDS.Tables[0].Rows[0]["doctype"]);
// _BnkStmtLine.SetC_BPartner_ID(Convert.ToInt32(PyDS.Tables[0].Rows[0]["c_bpartner_id"]));
// _BnkStmtLine.SetC_Invoice_ID(Convert.ToInt32(PyDS.Tables[0].Rows[0]["c_invoice_id"]));
// if (_c_payment_id != null && _c_payment_id != 0)
// {
// if (_doctype == "AP Payment")
// {
// _BnkStmtLine.SetC_Payment_ID(_c_payment_id);
// _BnkStmtLine.SetStmtAmt(Convert.ToDecimal("-" + _payAmt));
// _BnkStmtLine.SetTrxAmt(Convert.ToDecimal("-" + _payAmt));
// }
// else if (_doctype == "AR Receipt")
// {
// _BnkStmtLine.SetC_Payment_ID(_c_payment_id);
// _BnkStmtLine.SetStmtAmt(_payAmt);
// _BnkStmtLine.SetTrxAmt(_payAmt);
// }
// }
// }
// else
// {
// if ((Convert.ToString(dt.Rows[i][7]) != string.Empty) && (Convert.ToString(dt.Rows[i][7]) != "0"))
// {
// _BnkStmtLine.SetStmtAmt(Convert.ToDecimal("-" + _payAmt));
// _BnkStmtLine.SetTrxAmt(Convert.ToDecimal("-" + _payAmt));
// }
// else
// {
// _BnkStmtLine.SetStmtAmt(_payAmt);
// _BnkStmtLine.SetTrxAmt(_payAmt);
// }
// }
//}
if (!_BnkStmtLine.Save())
{
}
}
}
#endregion
_BnkStatm.SetEndingBalance(_BnkStatm.GetBeginningBalance() + _BnkStatm.GetStatementDifference());
if (!_BnkStatm.Save())
{
_obj._error = "VA012_BeginningBalanceNotUpdated";
// _obj._error = "Beginning Balance Not Updated";
return(_obj);
}
}
}
else
{
_obj._error = "VA012_NoRecordsInExcel";
return(_obj);
}
#endregion
}
else
{
_obj._error = "VA012_NoRecordsInExcel";
return(_obj);
}
}
else
{
_obj._error = "VA012_FormatNotSupported";
return(_obj);
}
}
_obj._statementID = _stementID.ToString();
return(_obj);
}
public StatementResponse ImportStatement(Ctx ctx, string FileName, string _path, int _bankaccount, int _bankAccountCurrency, string _statementno, string _statementCharges)
{
StatementResponse _obj = new 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;
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;
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);
}
// New Files To Update In Our System
_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();
if ((_Extension.ToUpper() == ".CSV") || (_Extension.ToUpper() == ".XLSX") || (_Extension.ToUpper() == ".XLS"))
{
DataSet ds = ExcelImport.ImportFromCSV(_path, false, 1);
if (File.Exists(_path))
{
FileInfo fileToDelete = new FileInfo(_path);
//fileToDelete.Delete();
}
if (ds != null)
{
DataTable dt = ds.Tables[0];
string _date = "";
string[] _dateList = new string[3];
if (dt.Rows.Count > 0)
{
#region ENBD Format
if (dt.Columns[0].ColumnName == "F1" && Convert.ToString(dt.Rows[0][0]) == "Account Statement")
{
////Change Culture to Server Culture
//Thread.CurrentThread.CurrentCulture.ClearCachedData();
// _serverCulture = Thread.CurrentThread.CurrentCulture.Name;
// _serverCultureAC = "";
//if (_serverCulture != "en-US")
//{
// Thread.CurrentThread.CurrentCulture = new CultureInfo(_serverCulture);
//}
/////
// _serverCultureAC = Thread.CurrentThread.CurrentCulture.Name;
string accountCurrency = Convert.ToString(dt.Rows[5][0]);
if (string.IsNullOrEmpty(accountCurrency))
{
accountCurrency = Convert.ToString(dt.Rows[4][0]);
}
//_C_Currency_ID = Convert.ToInt32(DB.ExecuteScalar("Select C_Currency_ID from C_Currency Where iso_code= 'AED'"));
accountCurrency = accountCurrency.Substring(accountCurrency.IndexOf(':') + 1).Trim();
_C_Currency_ID = Convert.ToInt32(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 (Convert.ToString(dt.Rows[0][0]) != "Account Statement")
//{
// break;
//}
if (i <= 8)
{
if (i == 3)
{
_IBAN = Convert.ToString(dt.Rows[i][0]);
_IBAN = _IBAN.Substring(_IBAN.LastIndexOf(':') + 1).Trim();
}
if (i == 7)
{
_branchName = Convert.ToString(dt.Rows[i][0]);
_branchName = _branchName.Substring(_branchName.LastIndexOf(':') + 1).Trim();
}
continue;
}
#region Header
if (i == 9)
{
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);
// _BnkStatm.SetBeginningBalance(Convert.ToDecimal(dt.Rows[i][9]));
if (!_BnkStatm.Save())
{
_obj._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 ((Convert.ToString(dt.Rows[i][0]) != string.Empty) && (Convert.ToString(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();
}
//_datasetvalues = "DataTableRpw==> --0--" + dt.Rows[i][0].ToString() + "--1--" + dt.Rows[i][1].ToString() + "--2--" + dt.Rows[i][2].ToString() + "--3--" + dt.Rows[i][3].ToString() + "--4--" + dt.Rows[i][4].ToString();
_BnkStmtLine = new MBankStatementLine(_BnkStatm);
_BnkStmtLine.SetAD_Client_ID(ctx.GetAD_Client_ID());
_BnkStmtLine.SetAD_Org_ID(ctx.GetAD_Org_ID());
_BnkStmtLine.SetVA012_Page(pageno);
_BnkStmtLine.SetLine(lineno);
lineno = lineno + 10;
// abc = "onessss";
_BnkStmtLine.SetStatementLineDate(DateTime.Parse(_date)); // Set Transaction Date
_BnkStmtLine.SetDateAcct(DateTime.Parse(_date)); // Set Transaction Date
_BnkStmtLine.SetValutaDate(DateTime.Parse(_date)); // Set Transaction Date
// abc = "one";
_BnkStmtLine.SetReferenceNo(_IBAN); // Set Transaction Remarks
_BnkStmtLine.SetDescription(Convert.ToString(dt.Rows[i][1])); // Set Transaction Purticular
_BnkStmtLine.SetMemo(_branchName); // Set Deposite Branch
//_C_Currency_ID = Convert.ToInt32(DB.ExecuteScalar("Select C_Currency_ID from C_Currency Where iso_code= '" + (dt.Rows[i][6]) + "'"));
// abc = "two";
if (_C_Currency_ID > 0)
{
_BnkStmtLine.SetC_Currency_ID(_C_Currency_ID);// Set Currency Type
}
if ((Convert.ToString(dt.Rows[i][2]) != string.Empty) && (Convert.ToString(dt.Rows[i][2]) != "0"))
{
// abc = "three";
// _payAmt = Convert.ToDecimal(dt.Rows[i][2]);
_payAmt = GetAmount(dt.Rows[i][2].ToString(), isDiffCulture);
// abc = "four";
}
else
{
// abc = "five";
// abc = dt.Rows[i][3].ToString();
// _payAmt = Convert.ToDecimal(dt.Rows[i][3]);
_payAmt = GetAmount(dt.Rows[i][3].ToString(), isDiffCulture);
// abc = "six";
}
if (_accountType == "C")
{
if ((Convert.ToString(dt.Rows[i][2]) != string.Empty) && (Convert.ToString(dt.Rows[i][2]) != "0"))
{
_BnkStmtLine.SetStmtAmt(_payAmt);
_BnkStmtLine.SetTrxAmt(_payAmt);
}
else
{
_BnkStmtLine.SetStmtAmt(Convert.ToDecimal("-" + _payAmt));
_BnkStmtLine.SetTrxAmt(Convert.ToDecimal("-" + _payAmt));
}
}
else
{
if ((Convert.ToString(dt.Rows[i][2]) != string.Empty) && (Convert.ToString(dt.Rows[i][2]) != "0"))
{
_BnkStmtLine.SetStmtAmt(Convert.ToDecimal("-" + _payAmt));
_BnkStmtLine.SetTrxAmt(Convert.ToDecimal("-" + _payAmt));
}
else
{
_BnkStmtLine.SetStmtAmt(_payAmt);
_BnkStmtLine.SetTrxAmt(_payAmt);
}
}
// abc = "seven";
if (!_BnkStmtLine.Save())
{
}
}
else
{
}
}
#endregion
}
////Revert culture back to en-US
//Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
////
}
#endregion ENBD Format
#region HSBC Format
else if (dt.Columns[0].ColumnName == "Account Number")
{
_C_Currency_ID = Convert.ToInt32(DB.ExecuteScalar("Select C_Currency_ID from C_Currency Where iso_code= 'AED'"));
if (_C_Currency_ID != _bankAccountCurrency)
{
_obj._error = "VA012_DiffAccountAndStatementCurrency";
return(_obj);
}
for (int i = -1; i < dt.Rows.Count; i++)
{
#region Header
if (i == -1)
{
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);
// _BnkStatm.SetBeginningBalance(Convert.ToDecimal(dt.Rows[i][9]));
if (!_BnkStatm.Save())
{
_obj._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
{
if ((Convert.ToString(dt.Rows[i][0]) != string.Empty) && (Convert.ToString(dt.Rows[i][1]) != string.Empty))
{
_BnkStmtLine = new MBankStatementLine(_BnkStatm);
_BnkStmtLine.SetAD_Client_ID(ctx.GetAD_Client_ID());
_BnkStmtLine.SetAD_Org_ID(ctx.GetAD_Org_ID());
_BnkStmtLine.SetVA012_Page(pageno);
_BnkStmtLine.SetLine(lineno);
lineno = lineno + 10;
_BnkStmtLine.SetStatementLineDate(DateTime.Parse(dt.Rows[i][1].ToString())); // Set Transaction Date
_BnkStmtLine.SetDateAcct(DateTime.Parse(dt.Rows[i][1].ToString())); // Set Transaction Date
_BnkStmtLine.SetValutaDate(DateTime.Parse(dt.Rows[i][1].ToString())); // Set Transaction Date
// _BnkStmtLine.SetReferenceNo(Convert.ToString(dt.Rows[i][3]));// Set Transaction Remarks
_BnkStmtLine.SetDescription(Convert.ToString(dt.Rows[i][2])); // Set Transaction Purticular
// _BnkStmtLine.SetMemo(Convert.ToString(dt.Rows[i][10]));// Set Deposite Branch
//_C_Currency_ID = Convert.ToInt32(DB.ExecuteScalar("Select C_Currency_ID from C_Currency Where iso_code= '" + (dt.Rows[i][6]) + "'"));
if (_C_Currency_ID > 0)
{
_BnkStmtLine.SetC_Currency_ID(_C_Currency_ID);// Set Currency Type
}
if ((Convert.ToString(dt.Rows[i][3]) != string.Empty) && (Convert.ToString(dt.Rows[i][3]) != "0"))
{
_payAmt = Convert.ToDecimal(dt.Rows[i][3]);
}
else
{
_payAmt = Convert.ToDecimal(dt.Rows[i][4]);
}
if (_accountType == "C")
{
if ((Convert.ToString(dt.Rows[i][3]) != string.Empty) && (Convert.ToString(dt.Rows[i][3]) != "0"))
{
_BnkStmtLine.SetStmtAmt(_payAmt);
_BnkStmtLine.SetTrxAmt(_payAmt);
}
else
{
_BnkStmtLine.SetStmtAmt(Convert.ToDecimal("-" + _payAmt));
_BnkStmtLine.SetTrxAmt(Convert.ToDecimal("-" + _payAmt));
}
}
else
{
if ((Convert.ToString(dt.Rows[i][3]) != string.Empty) && (Convert.ToString(dt.Rows[i][3]) != "0"))
{
_BnkStmtLine.SetStmtAmt(Convert.ToDecimal("-" + _payAmt));
_BnkStmtLine.SetTrxAmt(Convert.ToDecimal("-" + _payAmt));
}
else
{
_BnkStmtLine.SetStmtAmt(_payAmt);
_BnkStmtLine.SetTrxAmt(_payAmt);
}
}
if (!_BnkStmtLine.Save())
{
}
}
else
{
}
}
#endregion
}
}
#endregion HSBC Format
else
{
_obj._error = "VA012_ErrorInFileFormat";
return(_obj);
}
}
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" + "|" + e.Message + "|" + _branchName + "|" + _IBAN + "|" + abc + "|" + _serverCulture + "|" + _serverCultureAC + "||||" + _datasetvalues;
_obj._error = "VA012_ErrorInFileFormat";
return(_obj);
}
}
public StatementResponse ImportStatement(Ctx ctx, string FileName, string _path, int _bankaccount, int _bankAccountCurrency, string _statementno, string _statementCharges)
{
StatementResponse _obj = new StatementResponse();
try
{
_C_Currency_ID = Convert.ToInt32(DB.ExecuteScalar("Select C_Currency_ID from C_Currency Where iso_code= 'AED'"));
if (_C_Currency_ID != _bankAccountCurrency)
{
_obj._error = "VA012_DiffAccountAndStatementCurrency";
return(_obj);
}
#region Period StartDate and End Date
DateTime?_startdate = null;
DateTime?_enddate = null;
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;
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);
}
// New Files To Update In Our System
_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();
if ((_Extension.ToUpper() == ".CSV") || (_Extension.ToUpper() == ".XLSX") || (_Extension.ToUpper() == ".XLS"))
{
DataSet ds = ExcelImport.ImportFromCSV(_path, false);
if (File.Exists(_path))
{
FileInfo fileToDelete = new FileInfo(_path);
fileToDelete.Delete();
}
if (ds != null)
{
DataTable dt = ds.Tables[0];
if (dt.Rows.Count > 0)
{
for (int i = -1; i < dt.Rows.Count; i++)
{
#region Header
if (i == -1)
{
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);
// _BnkStatm.SetBeginningBalance(Convert.ToDecimal(dt.Rows[i][9]));
if (!_BnkStatm.Save())
{
_obj._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
{
if ((Convert.ToString(dt.Rows[i][0]) != string.Empty) && (Convert.ToString(dt.Rows[i][1]) != string.Empty))
{
_BnkStmtLine = new MBankStatementLine(_BnkStatm);
_BnkStmtLine.SetAD_Client_ID(ctx.GetAD_Client_ID());
_BnkStmtLine.SetAD_Org_ID(ctx.GetAD_Org_ID());
_BnkStmtLine.SetVA012_Page(pageno);
_BnkStmtLine.SetLine(lineno);
lineno = lineno + 10;
_BnkStmtLine.SetStatementLineDate(DateTime.Parse(dt.Rows[i][1].ToString())); // Set Transaction Date
_BnkStmtLine.SetDateAcct(DateTime.Parse(dt.Rows[i][1].ToString())); // Set Transaction Date
_BnkStmtLine.SetValutaDate(DateTime.Parse(dt.Rows[i][1].ToString())); // Set Transaction Date
// _BnkStmtLine.SetReferenceNo(Convert.ToString(dt.Rows[i][3]));// Set Transaction Remarks
_BnkStmtLine.SetDescription(Convert.ToString(dt.Rows[i][2])); // Set Transaction Purticular
// _BnkStmtLine.SetMemo(Convert.ToString(dt.Rows[i][10]));// Set Deposite Branch
//_C_Currency_ID = Convert.ToInt32(DB.ExecuteScalar("Select C_Currency_ID from C_Currency Where iso_code= '" + (dt.Rows[i][6]) + "'"));
if (_C_Currency_ID > 0)
{
_BnkStmtLine.SetC_Currency_ID(_C_Currency_ID);// Set Currency Type
}
if ((Convert.ToString(dt.Rows[i][3]) != string.Empty) && (Convert.ToString(dt.Rows[i][3]) != "0"))
{
_payAmt = Convert.ToDecimal(dt.Rows[i][3]);
}
else
{
_payAmt = Convert.ToDecimal(dt.Rows[i][4]);
}
if (_accountType == "C")
{
if ((Convert.ToString(dt.Rows[i][3]) != string.Empty) && (Convert.ToString(dt.Rows[i][3]) != "0"))
{
_BnkStmtLine.SetStmtAmt(_payAmt);
_BnkStmtLine.SetTrxAmt(_payAmt);
}
else
{
_BnkStmtLine.SetStmtAmt(Convert.ToDecimal("-" + _payAmt));
_BnkStmtLine.SetTrxAmt(Convert.ToDecimal("-" + _payAmt));
}
}
else
{
if ((Convert.ToString(dt.Rows[i][3]) != string.Empty) && (Convert.ToString(dt.Rows[i][3]) != "0"))
{
_BnkStmtLine.SetStmtAmt(Convert.ToDecimal("-" + _payAmt));
_BnkStmtLine.SetTrxAmt(Convert.ToDecimal("-" + _payAmt));
}
else
{
_BnkStmtLine.SetStmtAmt(_payAmt);
_BnkStmtLine.SetTrxAmt(_payAmt);
}
}
if (!_BnkStmtLine.Save())
{
}
}
else
{
}
}
#endregion
}
}
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);
}
}
/// <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);
}
}
