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