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