Beispiel #1
0
        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);
            }
        }
Beispiel #2
0
        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);
        }
Beispiel #3
0
        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);
            }
        }
Beispiel #4
0
        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);
        }
Beispiel #5
0
        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);
            }
        }