private void Form1_Load(object sender, EventArgs e)
{
using (AccountsDataContext obj = new AccountsDataContext())
{
obj.UpdateFiscalYearOnlogin(DateTime.Now.AddDays(1));
}
}
public static List <ShowChartOfAccountsResult> ShowChartOfAccounts()
{
using (AccountsDataContext obj = new AccountsDataContext())
{
return(obj.ShowChartOfAccounts().ToList());
}
}
public static void InsertJournalAndPosting(String AccountId, String AccountNum, String Amount, String Description, String Type, DateTime eDate)
{
using (AccountsDataContext obj = new AccountsDataContext())
{
obj.InsertJournalAndPosting(Convert.ToInt32(AccountId), AccountNum,
Convert.ToSingle(Amount), Description, Type, eDate);
}
}
public static String[] ShowAccountsInfo()
{
String[] Set;
using (AccountsDataContext obj = new AccountsDataContext())
{
List <ShowChartOfAccountsResult> Get = obj.ShowChartOfAccounts().ToList();
Set = new String[Get.Count];
for (int i = 0; i < Get.Count; i++)
{
Set[i] = Get[i].ID + "|" + Get[i].ACCOUNT_ID + "|" + Get[i].NAME;
}
}
return(Set);
}
public static void UpdateFiscalYearForLogin()
{
using (AccountsDataContext obj = new AccountsDataContext())
{
try
{
obj.UpdateFiscalYearOnlogin(DateTime.Now.AddDays(1));
}
catch (Exception)
{
throw;
}
}
}
public static String[] ShowLedgerTypes()
{
String[] Set;
using (AccountsDataContext obj = new AccountsDataContext())
{
List <ShowLedgersResult> Get = obj.ShowLedgers().ToList();
Set = new String[Get.Count];
for (int i = 0; i < Get.Count; i++)
{
Set[i] = Get[i].type;
}
}
return(Set);
}
public static String[] ShowFiscalYEar()
{
String[] Set;
using (AccountsDataContext obj = new AccountsDataContext())
{
List <ShowFiscalYearResult> Get = obj.ShowFiscalYear().ToList();
Set = new String[Get.Count];
for (int i = 0; i < Get.Count; i++)
{
Set[i] = Convert.ToDateTime(Get[i].f_from.ToString()).ToShortDateString() + "|" + Convert.ToDateTime(Get[i].f_to.ToString()).ToShortDateString();
}
}
return(Set);
}
public static bool CheckNames(String NameOfAccount)
{
using (AccountsDataContext obj = new AccountsDataContext())
{
List <CheckNameOfChartOfAccountsResult> Get = obj.CheckNameOfChartOfAccounts(NameOfAccount).ToList();
if (Get.Count > 0)
{
return(false);
}
else
{
return(true);
}
}
}
public static bool InsertFiscalYear(DateTime ClosingTo)
{
try
{
using (AccountsDataContext obj = new AccountsDataContext())
{
DateTime FromNew = ClosingTo;
DateTime ToNew = DateTime.Now;
obj.InsertFiscalYear(ClosingTo, ToNew, ClosingTo);
}
return(true);
}
catch (Exception ex)
{
return(false);
}
}
// Gather all data
public void CheckPayment()
{
int weekDiff = 0;
int monthDiff = 0;
DateTimeFormatInfo dfi = DateTimeFormatInfo.CurrentInfo;
Calendar cal = dfi.Calendar;
Console.Write("Checking Payment... ");
// Get current week distance to OK date
using (var billPaymentDB = new BillPaymentDataContext())
{
lastOkDate = billPaymentDBDB.CheckDates.ToList()[0].OnDate;
weekDiff = cal.GetWeekOfYear(DateTime.Now, dfi.CalendarWeekRule, dfi.FirstDayOfWeek) - cal.GetWeekOfYear(lastOkDate, dfi.CalendarWeekRule, dfi.FirstDayOfWeek);
monthDiff = (((DateTime.Now.Year - lastOkDate.Year) * 12) + DateTime.Now.Month - lastOkDate.Month);
// Work out how many bills in between ok date and month that each tenant has to pay
PayDetails = (
from BillPayment in billPaymentDBDB.BillPayments.ToList()
join Tenant in billPaymentDBDB.Tenants.ToList() on BillPayment.TenantId equals Tenant.Id
join Bill in billPaymentDBDB.Bills.ToList() on BillPayment.BillId equals Bill.Id
select new PayDetail
{
BillName = Bill.Desc,
TenantName = Tenant.Name,
Amount = BillPayment.Amount,
KeyWords = BillPayment.KeyWords,
DueTimes = (
Bill.Frequency == "W" ? weekDiff :
Bill.Frequency == "M" ? monthDiff : 0
),
PaidTimes = 0
}).ToList();
// Work out distinct bill amounts to take transaction into account
var distinctPayAmts = PayDetails.GroupBy(x => x.Amount).Select(x => x.First()).ToList();
// Set up transaction placeholder
Payments = new Dictionary <string, List <CBACompleteAccess> >();
foreach (var tenant in billPaymentDBDB.Tenants)
{
Payments[tenant.Name.Split(' ')[0].ToLower()] = new List <CBACompleteAccess>();
}
// Go through all transactions count how many times who paid what and increment the payment count if match
using (var transDB = new AccountsDataContext())
{
// Filter by incoming transfer > tenant > bill by amount
var incomingTrnsfr = transDB.CBACompleteAccesses.Where(x => x.TransDesc.ToLower().StartsWith("transfer from") || x.TransDesc.ToLower().StartsWith("direct credit")).ToList();
var tenantsTrnsfr = incomingTrnsfr.Where(x => Payments.Keys.Any(w => x.TransDesc.ToLower().Contains(w))).ToList();
var billTrnsfr = tenantsTrnsfr.Where(x => distinctPayAmts.Any(a => decimal.Equals(x.TransAmount, a.Amount))).ToList();
// Split transactions by tenants
foreach (var tenant in Payments.Keys.ToList())
{
Payments[tenant] = billTrnsfr.Where(x => x.TransDesc.ToLower().Contains(tenant)).ToList();
// Count paid bills
foreach (var pay in PayDetails)
{
if (pay.TenantName.ToLower().StartsWith(tenant))
{
pay.PaidTimes = Payments[tenant].Count(x => decimal.Equals(x.TransAmount, pay.Amount));
}
}
}
}
}
Console.WriteLine("DONE");
}
public static void InsertChartOfAccounts(String Name, String AccountType, DateTime E_date)
{
try
{
String Query = "select max(cast(substring(num,2,10) as int)) from chartofaccounts where (type = '" + AccountType + "')";
String AccountNum = String.Empty;
String ID = String.Empty;
try
{
DBO.AccountsConnection = new SqlConnection(DBO.Connection);
int IncrementID = 0;
DBO.AccountsConnection.Open();
DBO.AccountsCommand = new SqlCommand(Query, DBO.AccountsConnection);
var GetId = DBO.AccountsCommand.ExecuteScalar();
IncrementID = (Convert.ToInt32(GetId));
IncrementID++;
ID = (IncrementID).ToString();
DBO.AccountsConnection.Close();
}
catch
{
DBO.AccountsConnection.Close();
ID = "1";
}
if (ID.Length == 1)
{
AccountNum = "00" + ID;
}
else if (ID.Length > 1)
{
AccountNum = "0" + ID;
}
if (AccountType == "Assets")
{
AccountNum = "1" + AccountNum;
}
else if (AccountType == "Equity")
{
AccountNum = "2" + AccountNum;
}
else if (AccountType == "Expense")
{
AccountNum = "3" + AccountNum;
}
else if (AccountType == "Liability")
{
AccountNum = "4" + AccountNum;
}
else if (AccountType == "Revenue")
{
AccountNum = "5" + AccountNum;
}
using (AccountsDataContext obj = new AccountsDataContext())
{
obj.InsertChartOfAccounts(AccountNum, Name, AccountType, E_date);
}
}
catch (Exception ex)
{
}
}