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