Exemple #1
0
        public bool SaveAccountInfo(Account account)
        {
            foreach (var accountInfo in account.AccountInfo)
            {
                var sql = $"INSERT INTO AccountInfo(ArCode, TranDate, TranDetail, DueDate, InvoiceNumber, ReferenceNumber)  VALUES('{account.AccountHeader.ArCode}','{accountInfo.TranDate}','{accountInfo.TranDetail}'," +
                          $"'{accountInfo.DueDate}','{accountInfo.InvoiceNumber}','{accountInfo.ReferenceNumber}')";
                using (SqlConnection sqlCon = new SqlConnection(Config.ConnString))
                {
                    sqlCon.Open();
                    using (SqlCommand cmd = new SqlCommand(sql, sqlCon))
                    {
                        try
                        {
                            cmd.ExecuteNonQuery();
                        }
                        catch (Exception ex)
                        {
                            var err = new CreateLogFiles();
                            err.ErrorLog(Config.DataPath + "err.log", ex + "Error with this line: " + sql);
                            return(false);

                            throw;
                        }
                    }
                }
            }
            return(true);
        }
Exemple #2
0
        public static bool DeleteNegativeAndZeroAccounts()
        {
            var ars = GetNegativeAndZeroArs();

            try
            {
                using (SqlConnection sqlCon = new SqlConnection(Config.ConnString))
                {
                    sqlCon.Open();
                    foreach (var ar in ars)
                    {
                        var sql = $"DELETE FROM Accountheader WHERE Arcode = '{ar}'";
                        using (SqlCommand cmd = new SqlCommand(sql, sqlCon))
                        {
                            cmd.ExecuteNonQuery();
                        }
                    }
                }
                return(true);
            }
            catch (Exception ex)
            {
                var errLog = new CreateLogFiles();
                errLog.ErrorLog(Config.DataPath, "Error deleting negatives and zero accounts" + ex.Message);
                return(false);

                throw;
            }
        }
Exemple #3
0
 public void DeletePaidAccounts(Account account)
 {
     foreach (var accountInfo in account.AccountInfo)
     {
         var sql = "IF EXISTS " +
                   $"(SELECT * FROM AccountInfo WHERE ArCode  = ('{account.AccountHeader.ArCode}') AND TranDate = ('{accountInfo.TranDate}') AND TranDetail = ('{accountInfo.TranDetail}') AND InvoiceNumber = ('{accountInfo.InvoiceNumber}')) " +
                   "BEGIN " +
                   $"DELETE FROM AccountInfo WHERE ArCode  = ('{account.AccountHeader.ArCode}') AND TranDate = ('{accountInfo.TranDate}') AND TranDetail = ('{accountInfo.TranDetail}') AND InvoiceNumber = ('{accountInfo.InvoiceNumber}') " +
                   "END";
         using (SqlConnection sqlCon = new SqlConnection(Config.ConnString))
         {
             sqlCon.Open();
             using (SqlCommand cmd = new SqlCommand(sql, sqlCon))
             {
                 try
                 {
                     cmd.ExecuteNonQuery();
                 }
                 catch (Exception ex)
                 {
                     var err = new CreateLogFiles();
                     err.ErrorLog(Config.DataPath, "Error deleting this record:" + sql + "Error message:" + ex);
                     throw;
                 }
             }
         }
     }
 }
Exemple #4
0
        public bool SaveAccountHeader(Account account)
        {
            string sql;

            if (account != null && !CheckIfAccountHeaderExists(account))
            {
                sql = $"INSERT INTO AccountHeader(ArCode, AccountName, AccountPhoneNumber) VALUES ('{account.AccountHeader.ArCode}','{account.AccountHeader.AccountName}','{account.AccountHeader.AccountPhoneNumber}') ";
            }
            else
            {
                return(false);
            }

            using (SqlConnection sqlcon = new SqlConnection(Config.ConnString))
            {
                sqlcon.Open();
                using (SqlCommand cmd = new SqlCommand(sql, sqlcon))
                {
                    try
                    {
                        cmd.ExecuteNonQuery();
                        return(true);
                    }
                    catch (Exception ex)
                    {
                        var err = new CreateLogFiles();
                        err.ErrorLog(Config.DataPath + "err.log ", "Error inserting account header " + "Error on this command" + sql + ex);
                        return(false);

                        throw;
                    }
                }
            }
        }
Exemple #5
0
        public bool SaveFileToDb(string pathToTempFile, ProcessFile.TypeOfFile typeOfFile)
        {
            Byte[]   bytes         = ConvertFileToBytes(pathToTempFile);
            string   fileName      = Path.GetFileName(pathToTempFile);
            string   fileExtension = Path.GetExtension(pathToTempFile);
            DateTime dateTime      = DateTime.Now;

            using (SqlConnection sqlCon = new SqlConnection(Config.ConnString))
            {
                sqlCon.Open();
                string sql = $"INSERT INTO Files(FileName,FileExtension,DataFile,CreatedOn,TypeOfFile) VALUES(@FileName, @FileExtension,@DataFile,@CreatedOn,@TypeOfFile)";
                using (SqlCommand cmd = new SqlCommand(sql, sqlCon))
                {
                    try
                    {
                        cmd.Parameters.Add("@FileName", SqlDbType.VarChar).Value      = fileName;
                        cmd.Parameters.Add("@FileExtension", SqlDbType.VarChar).Value = fileExtension;
                        cmd.Parameters.Add("@DataFile", SqlDbType.Binary).Value       = bytes;
                        cmd.Parameters.Add("@CreatedOn", SqlDbType.DateTime).Value    = dateTime;
                        cmd.Parameters.Add("@TypeOfFile", SqlDbType.VarChar).Value    = typeOfFile;

                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        var err = new CreateLogFiles();
                        err.ErrorLog(Config.DataPath + "err.log", ex + "Error saving file to DB: " + sql);
                        return(false);

                        throw;
                    }
                }
            }
            return(true);
        }
Exemple #6
0
        public bool SaveTotalAr(InvoiceBalance invoiceBalance)
        {
            using (SqlConnection sqlCon = new SqlConnection(Config.ConnString))
            {
                sqlCon.Open();
                var sql = "INSERT INTO TotalAR(UploadDate,Balance,Curr,Over30,Over60,Over90) " +
                          $"VALUES('{DateTime.Now}',{invoiceBalance.Balance},{invoiceBalance.Curr},{invoiceBalance.Over30},{invoiceBalance.Over60},{invoiceBalance.Over90})";
                using (SqlCommand cmd = new SqlCommand(sql, sqlCon))
                {
                    try
                    {
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        var err = new CreateLogFiles();
                        err.ErrorLog(Config.DataPath + "err.log", ex.Message + "Error inserting total ar: " + sql);
                        return(false);

                        throw;
                    }
                }
            }
            return(true);
        }
Exemple #7
0
        public bool SaveAccountBalances(Account account)
        {
            var transactionId = GetTransactionIds(account.AccountHeader.ArCode);
            int i             = 0;

            foreach (var accountBalance in account.Balances)
            {
                var sql = $"INSERT INTO InvoiceBalance(ArCode,TransactionId, Balance,Curr,Over30,Over60,Over90) VALUES('{accountBalance.ArCode}',{transactionId[i]},{accountBalance.Balance},{accountBalance.Curr}," +
                          $"{accountBalance.Over30},{accountBalance.Over60},{accountBalance.Over90})";
                using (SqlConnection sqlCon = new SqlConnection(Config.ConnString))
                {
                    sqlCon.Open();
                    using (SqlCommand cmd = new SqlCommand(sql, sqlCon))
                    {
                        try
                        {
                            cmd.ExecuteNonQuery();
                        }
                        catch (Exception ex)
                        {
                            var err = new CreateLogFiles();
                            err.ErrorLog(Config.DataPath + "err.log", ex + "Error saving account balances int this line: " + sql);
                            return(false);

                            throw;
                        }
                    }
                }
                i++;
            }
            return(true);
        }
        public decimal GetTranBalance(string ArCode, int TranId)
        {
            var    balance = new decimal();
            string sql     = string.Empty;

            try
            {
                using (SqlConnection sqlCon = new SqlConnection(Config.ConnString))
                {
                    sqlCon.Open();
                    sql = $"SELECT DISTINCT Balance FROM InvoiceBalance WHERE ArCode = '{ArCode}' AND TransactionId = {TranId}";
                    using (SqlCommand cmd = new SqlCommand(sql, sqlCon))
                    {
                        using (SqlDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                if (reader.FieldCount == 1 && !reader.IsDBNull(0))
                                {
                                    balance = Convert.ToDecimal(reader.GetValue(0));
                                }
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                var Err = new CreateLogFiles();
                Err.ErrorLog(Config.WebDataPath + "err.log", "Error on GetTranBalance" + ex.Message + "Error on this query" + sql);
                throw;
            }
            return(balance);
        }
        public List <AccountInfo> GetAccountInfo(string arCode)
        {
            var    accounts = new List <AccountInfo>();
            string sql      = string.Empty;

            try
            {
                using (SqlConnection sqlCon = new SqlConnection(Config.ConnString))
                {
                    sqlCon.Open();
                    sql = $"SELECT * FROM AccountInfo WHERE ArCode = '{arCode}'";
                    using (SqlCommand cmd = new SqlCommand(sql, sqlCon))
                    {
                        using (SqlDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                var accountInfo = new AccountInfo();

                                for (int i = 0; i < reader.FieldCount; i++)
                                {
                                    var fieldName = reader.GetName(i);
                                    var property  = accountInfo.GetType().GetProperty(fieldName);

                                    if (property != null && !reader.IsDBNull(i))
                                    {
                                        property.SetValue(accountInfo, reader.GetValue(i), null);
                                    }
                                }
                                accounts.Add(accountInfo);
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                var Err = new CreateLogFiles();
                Err.ErrorLog(Config.WebDataPath + "err.log", "Error on Displaying account info" + ex.Message + "Error on this query" + sql);
                throw;
            }

            return(accounts);
        }
Exemple #10
0
 private static Byte[] ConvertFileToBytes(string pathToTempFile)
 {
     Byte[] bytes;
     using (FileStream fs = new FileStream(pathToTempFile, FileMode.Open, FileAccess.Read))
     {
         using (BinaryReader br = new BinaryReader(fs))
         {
             try
             {
                 bytes = br.ReadBytes((Int32)fs.Length);
             }
             catch (Exception ex)
             {
                 var err = new CreateLogFiles();
                 err.ErrorLog(Config.DataPath + "err.log", ex + "Error file to DB");
                 throw;
             }
         }
     }
     return(bytes);
 }
        public InvoiceBalance GetInvoiceBalance(string arCode, string transactionId)
        {
            var    invoiceBalance = new InvoiceBalance();
            string sql            = string.Empty;

            try
            {
                using (SqlConnection sqlCon = new SqlConnection(Config.ConnString))
                {
                    sqlCon.Open();
                    sql = $"SELECT DISTINCT * FROM InvoiceBalance WHERE ArCode = '{arCode}' AND TransactionId = {transactionId}";
                    using (SqlCommand cmd = new SqlCommand(sql, sqlCon))
                    {
                        using (SqlDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                for (int i = 0; i < reader.FieldCount; i++)
                                {
                                    var fieldName = reader.GetName(i);
                                    var property  = invoiceBalance.GetType().GetProperty(fieldName);

                                    if (property != null && !reader.IsDBNull(i))
                                    {
                                        property.SetValue(invoiceBalance, reader.GetValue(i), null);
                                    }
                                }
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                var Err = new CreateLogFiles();
                Err.ErrorLog(Config.WebDataPath + "err.log", "Error on GetInvoiceBalance" + ex.Message + "Error on this query" + sql);
                throw;
            }
            return(invoiceBalance);
        }
        public bool GetLastComment(List <AccountInfo> accountInfos)
        {
            using (SqlConnection sqlCon = new SqlConnection(Config.ConnString))
            {
                sqlCon.Open();
                foreach (var accountInfo in accountInfos)
                {
                    var sql = $"SELECT TOP 1 * FROM Comment WHERE  TransactionId = {accountInfo.TransactionId} ORDER BY CommentId DESC";
                    try
                    {
                        using (SqlCommand cmd = new SqlCommand(sql, sqlCon))
                        {
                            using (SqlDataReader reader = cmd.ExecuteReader())
                            {
                                while (reader.Read())
                                {
                                    if (!reader.IsDBNull(0))
                                    {
                                        accountInfo.LastComment = reader["CommentText"].ToString();
                                    }
                                }
                            }
                        }
                    }

                    catch (Exception ex)
                    {
                        var Err = new CreateLogFiles();
                        Err.ErrorLog(Config.WebDataPath + "err.log", "Error on Downloding previos file" + ex.Message);
                        return(false);

                        throw;
                    }
                }
            }

            return(true);
        }
Exemple #13
0
        public bool EditAccountHeader(AccountHeader accountHeader)
        {
            using (SqlConnection sqlCon = new SqlConnection(Config.ConnString))
            {
                sqlCon.Open();
                var sql = $"UPDATE AccountHeader SET AccountName = '{accountHeader.AccountName}', AccountPhoneNumber = '{accountHeader.AccountPhoneNumber}' WHERE ArCode = '{accountHeader.ArCode}'";
                using (SqlCommand cmd = new SqlCommand(sql, sqlCon))
                {
                    try
                    {
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        var err = new CreateLogFiles();
                        err.ErrorLog(Config.DataPath + "err.log", ex.Message + "Error updating accountHeader: " + sql);
                        return(false);

                        throw;
                    }
                }
            }
            return(true);
        }
Exemple #14
0
        public bool SaveComment(Comment comment)
        {
            using (SqlConnection sqlCon = new SqlConnection(Config.ConnString))
            {
                sqlCon.Open();
                var sql = $"INSERT INTO Comment(ArCode, TransactionId, CommentText, CommentTime) VALUES('{comment.ArCode}','{comment.TransactionId}','{comment.CommentText}', '{DateTime.Now}')";
                using (SqlCommand cmd = new SqlCommand(sql, sqlCon))
                {
                    try
                    {
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        var err = new CreateLogFiles();
                        err.ErrorLog(Config.DataPath + "err.log", ex.Message + "Error inserting comment: " + sql);
                        return(false);

                        throw;
                    }
                }
            }
            return(true);
        }
        public FileStream DownloadFileToProjectFolder(string pathToData, int id)
        {
            //the index number to write bytes to
            long CurrentIndex = 0;

            //the number of bytes to store in the array
            int BufferSize = 100;

            //The Number of bytes returned from GetBytes() method
            long BytesReturned;

            //A byte array to hold the buffer
            byte[] Blob = new byte[BufferSize];
            try
            {
                using (SqlConnection sqlCon = new SqlConnection(Config.ConnString))
                {
                    sqlCon.Open();
                    var sql = $"SELECT * FROM Files WHERE DocumentId = {id}";
                    using (SqlCommand cmd = new SqlCommand(sql, sqlCon))
                    {
                        using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
                        {
                            while (reader.Read())
                            {
                                FileStream   fs     = new FileStream(pathToData + "\\" + reader["FileName"].ToString(), FileMode.OpenOrCreate, FileAccess.Write);
                                BinaryWriter writer = new BinaryWriter(fs);

                                //reset the index to the beginning of the file
                                CurrentIndex = 0;
                                //the BlobsTable column indexCurrentIndex,
                                // the current index of the field from which to begin the read operationBlob,
                                // Array name to write tha buffer to0,
                                // the start index of the array to start the write operationBufferSize
                                // the maximum length to copy into the buffer);
                                BytesReturned = reader.GetBytes(3, CurrentIndex, Blob, 0, BufferSize);

                                while (BytesReturned == BufferSize)
                                {
                                    writer.Write(Blob);
                                    writer.Flush();
                                    CurrentIndex += BufferSize;
                                    BytesReturned = reader.GetBytes(3, CurrentIndex, Blob, 0, BufferSize);
                                }
                                var fileName = fs.Name;
                                writer.Write(Blob, 0, (int)BytesReturned); writer.Flush(); writer.Close(); fs.Close();
                                var file = File.OpenRead(fileName);
                                return(file);
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                var Err = new CreateLogFiles();
                Err.ErrorLog(Config.WebDataPath + "err.log", "Error on Downloding previos file" + ex.Message);
                return(null);

                throw;
            }
            return(null);
        }