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