public static void SaveSupplierType(Data_SupplierType supplierType)
{
try
{
using (NewcourtEntities ctx = new NewcourtEntities())
{
SupplierTypes record = ctx.SupplierTypes.FirstOrDefault(a => a.SupplierTypeCode == supplierType.SupplierTypeCode);
if (record != null)
{
record.Name = supplierType.Name;
}
else
{
ctx.SupplierTypes.Add(new SupplierTypes()
{
SupplierTypeCode = supplierType.SupplierTypeCode,
Name = supplierType.Name
});
}
ctx.SaveChanges();
}
}
catch (Exception ex)
{
throw ex;
}
}
public static List <Data_Payment> GetPayments(String username, int supplierId, String supplierTypeCode, String bankAccountCode, DateTime dateFrom, DateTime dateTo)
{
try
{
using (NewcourtEntities ctx = new NewcourtEntities())
{
return((from a in ctx.Payments
where (a.Username == username || username == String.Empty) &&
(a.SupplierID == supplierId || supplierId == 0) &&
(a.Suppliers.SupplierTypeCode == supplierTypeCode || supplierTypeCode == String.Empty) &&
(a.TimeProcessed >= dateFrom && a.TimeProcessed < dateTo)
select new Data_Payment()
{
PaymentID = a.PaymentID,
SupplierID = a.SupplierID,
BankAccountCode = a.BankAccountCode,
Username = a.Username,
Amount = a.Amount,
TimeProcessed = a.TimeProcessed,
FirstName = a.Suppliers.FirstName,
Surname = a.Suppliers.Surname,
SupplierTypeCode = a.Suppliers.SupplierTypeCode
}).ToList());
}
}
catch (Exception ex)
{
throw ex;
}
}
public static void SaveUser(Data_User user)
{
try
{
using (NewcourtEntities ctx = new NewcourtEntities())
{
Users record = ctx.Users.FirstOrDefault(a => a.Username == user.Username);
if (record != null)
{
record.Password = user.Password;
record.IsAdmin = user.IsAdmin ? (byte)1 : (byte)0;
}
else
{
ctx.Users.Add(new Users()
{
Username = user.Username,
Password = user.Password,
IsAdmin = user.IsAdmin ? (byte)1 : (byte)0
});
}
ctx.SaveChanges();
}
}
catch (Exception ex)
{
throw ex;
}
}
public static Data_User GetUserByUsername(String username)
{
Data_User user = null;
try
{
using (NewcourtEntities ctx = new NewcourtEntities())
{
user = (from a in ctx.Users
where a.Username == username
select new Data_User()
{
Username = a.Username,
Password = a.Password,
IsAdmin = a.IsAdmin == 1,
LastLoggedIn = a.LastLoggedIn
}).FirstOrDefault();
}
}
catch (Exception ex)
{
Utils.ShowException(ex);
}
return(user);
}
public static void SavePaymentStaging(String username, List <Data_PaymentStaging> payments)
{
try
{
using (NewcourtEntities ctx = new NewcourtEntities())
{
List <PaymentStaging> records = ctx.PaymentStaging.Where(a => a.Username == username).ToList();
foreach (var i in payments)
{
PaymentStaging current = records.FirstOrDefault(a => a.Username == i.Username && a.SupplierId == i.SuppplierId);
if (current != null)
{
current.Amount = i.Amount;
}
else
{
ctx.PaymentStaging.Add(new PaymentStaging()
{
Username = i.Username,
SupplierId = i.SuppplierId,
Amount = i.Amount
});
}
}
ctx.SaveChanges();
}
}
catch (Exception ex)
{
throw ex;
}
}
public static List <Data_PaymentStaging> GetPaymentStagingByUsername(String username)
{
try
{
using (NewcourtEntities ctx = new NewcourtEntities())
{
return((from a in ctx.PaymentStaging
where a.Username == username
orderby a.ID
select new Data_PaymentStaging()
{
ID = a.ID,
Username = a.Username,
SuppplierId = a.SupplierId,
Amount = a.Amount,
FirstName = a.Suppliers.FirstName,
Surname = a.Suppliers.Surname,
Address1 = a.Suppliers.Address1,
Address2 = a.Suppliers.Address2,
Address3 = a.Suppliers.Address3,
Address4 = a.Suppliers.Address4,
Address5 = a.Suppliers.Address5,
SupplierTypeCode = a.Suppliers.SupplierTypeCode
}).ToList());
}
}
catch (Exception ex)
{
throw ex;
}
}
public static void SaveBankAccount(Data_BankAccount bankAccount)
{
try
{
using (NewcourtEntities ctx = new NewcourtEntities())
{
BankAccounts record = ctx.BankAccounts.FirstOrDefault(a => a.BankAccountCode == bankAccount.BankAccountCode);
if (record != null)
{
record.BankAccountName = bankAccount.BankAccountName;
record.BIC = bankAccount.BIC;
record.IBAN = bankAccount.IBAN;
record.OIN = bankAccount.OIN;
}
else
{
ctx.BankAccounts.Add(new BankAccounts()
{
BankAccountCode = bankAccount.BankAccountCode,
BankAccountName = bankAccount.BankAccountName,
BIC = bankAccount.BIC,
IBAN = bankAccount.IBAN,
OIN = bankAccount.IBAN
});
}
ctx.SaveChanges();
}
}
catch (Exception ex)
{
throw ex;
}
}
public static bool DeleteSupplierType(String supplierTypeCode)
{
try
{
using (NewcourtEntities ctx = new NewcourtEntities())
{
if (CanDeleteSupplierType(supplierTypeCode))
{
SupplierTypes supplierType = ctx.SupplierTypes.FirstOrDefault(a => a.SupplierTypeCode == supplierTypeCode);
if (supplierType != null)
{
ctx.SupplierTypes.Remove(supplierType);
ctx.SaveChanges();
return(true);
}
}
return(false);
}
}
catch (Exception ex)
{
throw ex;
}
}
public static List <Data_Supplier> GetSuppliersWithPaymentStagingCheck(String username)
{
try {
List <Data_Supplier> suppliers = null;
using (NewcourtEntities ctx = new NewcourtEntities()) {
suppliers = (from a in ctx.Suppliers
select new Data_Supplier()
{
SupplierID = a.SupplierID,
SupplierTypeCode = a.SupplierTypeCode,
FirstName = a.FirstName,
Surname = a.Surname,
Address1 = a.Address1,
Address2 = a.Address2,
Address3 = a.Address3,
Address4 = a.Address4,
Address5 = a.Address5,
Phone = a.Phone,
Mobile = a.Mobile,
PPSVat = a.PPSVat,
BankName = a.BankName,
BankAddress1 = a.BankAddress1,
BankAddress2 = a.BankAddress2,
BankAddress3 = a.BankAddress3,
BankAddress4 = a.BankAddress4,
BankAddress5 = a.BankAddress5,
BankAccNumber = a.BankAccNumber,
SortCode = a.SortCode,
BIC = a.BIC,
IBAN = a.IBAN,
SupplierTypeName = a.SupplierTypes.Name,
IsValidForPayment = true
}).ToList();
List <Data_PaymentStaging> payments = Data_PaymentStaging.GetPaymentStagingByUsername(username);
if (payments != null)
{
foreach (var i in suppliers)
{
if (payments.Exists(a => a.SuppplierId == i.SupplierID))
{
i.PaymentExists = true;
}
if (String.IsNullOrEmpty(i.BIC) || String.IsNullOrEmpty(i.IBAN))
{
i.IsValidForPayment = false;
}
}
}
}
return(suppliers);
} catch (Exception ex) {
throw ex;
}
}
public static int GetSepaFileCount()
{
try {
using (NewcourtEntities ctx = new NewcourtEntities()) {
return(ctx.SystemParameters.FirstOrDefault().SepaFileCount ?? 0);
}
} catch (Exception ex) {
throw ex;
}
}
public static String GetCompanyName()
{
try {
using (NewcourtEntities ctx = new NewcourtEntities()) {
return(ctx.SystemParameters.FirstOrDefault().CompanyName);
}
} catch (Exception ex) {
throw ex;
}
}
public static String GetDatabaseVersion()
{
try {
using (NewcourtEntities ctx = new NewcourtEntities()) {
return(ctx.SystemParameters.FirstOrDefault().DatabaseVersion);
}
} catch (Exception ex) {
throw ex;
}
}
public static void DeleteAllPaymentStaging(String username)
{
try
{
using (NewcourtEntities ctx = new NewcourtEntities())
{
ctx.Database.ExecuteSqlCommand(String.Format("Delete from PaymentStaging where Username = '******'", username));
ctx.SaveChanges();
}
}
catch (Exception ex)
{
throw ex;
}
}
public static void SavePaymentRef(String paymentRef)
{
try {
using (NewcourtEntities ctx = new NewcourtEntities()) {
var item = ctx.SystemParameters.FirstOrDefault();
if (item != null)
{
item.PaymentRef = paymentRef;
ctx.SaveChanges();
}
}
} catch (Exception ex) {
throw ex;
}
}
public static void DeleteSupplier(int supplierID)
{
try {
using (NewcourtEntities ctx = new NewcourtEntities()) {
Suppliers supplier = ctx.Suppliers.FirstOrDefault(a => a.SupplierID == supplierID);
if (supplier != null)
{
ctx.Suppliers.Remove(supplier);
ctx.SaveChanges();
}
}
} catch (Exception ex) {
throw ex;
}
}
public static String GenerateSEPAPaymentsXML(String bankAccountCode, DateTime paymentDate, String paymentRef, String username, ref int batch)
{
try
{
using (NewcourtEntities ctx = new NewcourtEntities())
{
ObjectParameter output = new ObjectParameter("Batch", typeof(int));
String result = ctx.GenerateSEPAPaymentXML(bankAccountCode, paymentDate, paymentRef, username, output).FirstOrDefault();
batch = Convert.ToInt32(output.Value);
return(result);
}
}
catch (Exception ex)
{
throw ex;
}
}
public static SortableBindingList <Data_Supplier> GetSortableSuppliers()
{
try {
using (NewcourtEntities ctx = new NewcourtEntities()) {
SortableBindingList <Data_Supplier> list = new SortableBindingList <Data_Supplier>();
var result = (from a in ctx.Suppliers
select new Data_Supplier()
{
SupplierID = a.SupplierID,
SupplierTypeCode = a.SupplierTypeCode,
FirstName = a.FirstName,
Surname = a.Surname,
Address1 = a.Address1,
Address2 = a.Address2,
Address3 = a.Address3,
Address4 = a.Address4,
Address5 = a.Address5,
Phone = a.Phone,
Mobile = a.Mobile,
PPSVat = a.PPSVat,
BankName = a.BankName,
BankAddress1 = a.BankAddress1,
BankAddress2 = a.BankAddress2,
BankAddress3 = a.BankAddress3,
BankAddress4 = a.BankAddress4,
BankAddress5 = a.BankAddress5,
BankAccNumber = a.BankAccNumber,
SortCode = a.SortCode,
BIC = a.BIC,
IBAN = a.IBAN,
SupplierTypeName = a.SupplierTypes.Name,
PaymentExists = false
}).ToList();
foreach (var i in result)
{
list.Add(i);
}
return(list);
}
} catch (Exception ex) {
throw ex;
}
}
public static List <Data_SupplierType> GetSupplierTypes()
{
try
{
using (NewcourtEntities ctx = new NewcourtEntities())
{
return((from a in ctx.SupplierTypes
select new Data_SupplierType()
{
SupplierTypeCode = a.SupplierTypeCode,
Name = a.Name
}).ToList());
}
}
catch (Exception ex)
{
throw ex;
}
}
public static String GetPaymentRef()
{
String result = String.Empty;
try {
using (NewcourtEntities ctx = new NewcourtEntities()) {
var item = ctx.SystemParameters.FirstOrDefault();
if (item != null)
{
result = item.PaymentRef;
}
}
} catch (Exception ex) {
throw ex;
}
return(result);
}
public static void DeleteBankAccount(String bankAccountCode)
{
try
{
using (NewcourtEntities ctx = new NewcourtEntities())
{
BankAccounts record = ctx.BankAccounts.FirstOrDefault(a => a.BankAccountCode == bankAccountCode);
if (record != null)
{
ctx.BankAccounts.Remove(record);
ctx.SaveChanges();
}
}
}
catch (Exception ex)
{
throw ex;
}
}
public static void DeleteUser(String username)
{
try
{
using (NewcourtEntities ctx = new NewcourtEntities())
{
Users user = ctx.Users.FirstOrDefault(a => a.Username == username);
if (user != null)
{
ctx.Users.Remove(user);
ctx.SaveChanges();
}
}
}
catch (Exception ex)
{
throw ex;
}
}
public static Data_SupplierType GetSupplierType(String supplierTypeCode)
{
try
{
using (NewcourtEntities ctx = new NewcourtEntities())
{
return((from a in ctx.SupplierTypes
where a.SupplierTypeCode == supplierTypeCode
select new Data_SupplierType()
{
SupplierTypeCode = a.SupplierTypeCode,
Name = a.Name
}).FirstOrDefault());
}
}
catch (Exception ex)
{
throw ex;
}
}
public static void SavePaymentStaging(Data_PaymentStaging payment)
{
try
{
using (NewcourtEntities ctx = new NewcourtEntities())
{
PaymentStaging record = ctx.PaymentStaging.FirstOrDefault(a => a.Username == payment.Username && a.SupplierId == payment.SuppplierId);
if (record != null)
{
record.Amount = payment.Amount;
ctx.SaveChanges();
}
}
}
catch (Exception ex)
{
throw ex;
}
}
public static void DeleteFromPaymentStaging(String username, int supplierId)
{
try
{
using (NewcourtEntities ctx = new NewcourtEntities())
{
PaymentStaging payment = ctx.PaymentStaging.FirstOrDefault(a => a.Username == username && a.SupplierId == supplierId);
if (payment != null)
{
ctx.PaymentStaging.Remove(payment);
ctx.SaveChanges();
}
}
}
catch (Exception ex)
{
throw ex;
}
}
public static void SaveSystemParameters(Data_SystemParameters systemParams)
{
try {
using (NewcourtEntities ctx = new NewcourtEntities()) {
SystemParameters param = ctx.SystemParameters.FirstOrDefault();
if (param != null)
{
param.CompanyName = systemParams.CompanyName;
param.Address1 = systemParams.Address1;
param.Address2 = systemParams.Address2;
param.Address3 = systemParams.Address3;
param.Address4 = systemParams.Address4;
param.Address5 = systemParams.Address5;
param.Phone = systemParams.Phone;
param.VatRegNo = systemParams.VatRegNo;
param.HideSensitiveSupplierFields = systemParams.HideSensitiveSupplierFields == true ? (short)1 : (short)0;
}
else
{
ctx.SystemParameters.Add(new SystemParameters()
{
ID = 1,
CompanyName = systemParams.CompanyName,
Address1 = systemParams.Address1,
Address2 = systemParams.Address2,
Address3 = systemParams.Address3,
Address4 = systemParams.Address4,
Address5 = systemParams.Address5,
Phone = systemParams.Phone,
VatRegNo = systemParams.VatRegNo,
HideSensitiveSupplierFields = systemParams.HideSensitiveSupplierFields == true ? (short)1 : (short)0
});
}
ctx.SaveChanges();
}
} catch (Exception ex) {
throw ex;
}
}
public static List <Data_User> GetUsers()
{
try
{
using (NewcourtEntities ctx = new NewcourtEntities())
{
return((from a in ctx.Users
select new Data_User()
{
Username = a.Username,
Password = a.Password,
IsAdmin = a.IsAdmin == 1,
LastLoggedIn = a.LastLoggedIn
}).ToList());
}
}
catch (Exception ex)
{
throw ex;
}
}
private static bool CanDeleteSupplierType(String supplierTypeCode)
{
try
{
using (NewcourtEntities ctx = new NewcourtEntities())
{
// Check Suppliers
List <Data_Supplier> supplier = Data_Supplier.GetSuppliersBySupplierType(supplierTypeCode);
if (supplier.Count >= 1)
{
return(false);
}
}
return(true);
}
catch (Exception ex)
{
throw ex;
}
}
public static List <Data_BankAccount> GetBankAccounts()
{
try
{
using (NewcourtEntities ctx = new NewcourtEntities())
{
return((from a in ctx.BankAccounts
select new Data_BankAccount()
{
BankAccountCode = a.BankAccountCode,
BankAccountName = a.BankAccountName,
BIC = a.BIC,
IBAN = a.IBAN,
OIN = a.OIN
}).ToList());
}
}
catch (Exception ex)
{
throw ex;
}
}
public static Data_BankAccount GetBankAccount(String bankAccountCode)
{
try
{
using (NewcourtEntities ctx = new NewcourtEntities())
{
return((from a in ctx.BankAccounts
where a.BankAccountCode == bankAccountCode
select new Data_BankAccount()
{
BankAccountCode = a.BankAccountCode,
BankAccountName = a.BankAccountName,
BIC = a.BIC,
IBAN = a.IBAN,
OIN = a.OIN
}).FirstOrDefault());
}
}
catch (Exception ex)
{
throw ex;
}
}
public static List <Data_Supplier> GetSuppliersBySupplierType(String supplierTypeCode)
{
try {
using (NewcourtEntities ctx = new NewcourtEntities()) {
return((from a in ctx.Suppliers
where a.SupplierTypeCode == supplierTypeCode
select new Data_Supplier()
{
SupplierID = a.SupplierID,
SupplierTypeCode = a.SupplierTypeCode,
FirstName = a.FirstName,
Surname = a.Surname,
Address1 = a.Address1,
Address2 = a.Address2,
Address3 = a.Address3,
Address4 = a.Address4,
Address5 = a.Address5,
Phone = a.Phone,
Mobile = a.Mobile,
PPSVat = a.PPSVat,
BankName = a.BankName,
BankAddress1 = a.BankAddress1,
BankAddress2 = a.BankAddress2,
BankAddress3 = a.BankAddress3,
BankAddress4 = a.BankAddress4,
BankAddress5 = a.BankAddress5,
BankAccNumber = a.BankAccNumber,
SortCode = a.SortCode,
BIC = a.BIC,
IBAN = a.IBAN,
SupplierTypeName = a.SupplierTypes.Name
}).ToList());
}
} catch (Exception ex) {
throw ex;
}
}