/// <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> /// Perform Match /// </summary> /// <param name="bsl">bank statement line</param> /// <returns>Message</returns> private String Match(MBankStatementLine bsl) { if (_matchers == null || bsl == null || bsl.GetC_Payment_ID() != 0) { return("--"); } log.Fine("match - " + bsl); BankStatementMatchInfo info = null; for (int i = 0; i < _matchers.Length; i++) { if (_matchers[i].IsMatcherValid()) { info = _matchers[i].GetMatcher().FindMatch(bsl); if (info != null && info.IsMatched()) { if (info.GetC_Payment_ID() > 0) { bsl.SetC_Payment_ID(info.GetC_Payment_ID()); } if (info.GetC_Invoice_ID() > 0) { bsl.SetC_Invoice_ID(info.GetC_Invoice_ID()); } if (info.GetC_BPartner_ID() > 0) { bsl.SetC_BPartner_ID(info.GetC_BPartner_ID()); } bsl.Save(); return("OK"); } } } // for all matchers return("--"); }
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); } }