private void cbxAccount_MouseDoubleClick(object sender, System.Windows.Input.MouseButtonEventArgs e)
{
int id = Convert.ToInt32(((accounting_chart)cbxAccount.Data).id_chart);
if (id > 0)
{
accounting_journal accounting_journal = accounting_journalViewSource.View.CurrentItem as accounting_journal;
if (accounting_journal != null)
{
accounting_journal_detail _accounting_journal_detail = new entity.accounting_journal_detail();
_accounting_journal_detail.accounting_journal = accounting_journal;
if (db.app_currencyfx.Where(x => x.id_company == _setting.company_ID && x.is_active && x.app_currency.is_priority).FirstOrDefault() != null)
{
_accounting_journal_detail.id_currencyfx = db.app_currencyfx.Where(x => x.id_company == _setting.company_ID && x.is_active && x.app_currency.is_priority).FirstOrDefault().id_currencyfx;
_accounting_journal_detail.app_currencyfx = db.app_currencyfx.Where(x => x.id_company == _setting.company_ID && x.is_active && x.app_currency.is_priority).FirstOrDefault();
}
_accounting_journal_detail.is_head = true;
_accounting_journal_detail.accounting_chart = (accounting_chart)cbxAccount.Data;
_accounting_journal_detail.id_chart = id;
accounting_journal.accounting_journal_detail.Add(_accounting_journal_detail);
accounting_journalViewSource.View.Refresh();
accounting_journal_detailViewSource.View.Refresh();
}
}
}
private accounting_journal_detail create_Detail(ref accounting_journal accounting_journal, SqlDataReader chart_reader)
{
accounting_journal_detail accounting_journal_detail = new accounting_journal_detail();
using (db db = new db())
{
String DESPLANCUENTA = Convert.ToString(chart_reader["DESPLANCUENTA"]);
if (db.accounting_chart.Where(x => x.name == DESPLANCUENTA).FirstOrDefault() == null)
{
return(null);
}
accounting_journal_detail.id_chart = db.accounting_chart.Where(x => x.name == DESPLANCUENTA).FirstOrDefault().id_chart;
int COTIZACION = Convert.ToInt32(chart_reader["COTIZACION"]);
if (db.app_currencyfx.Where(x => x.buy_value == COTIZACION).FirstOrDefault() == null)
{
return(null);
}
accounting_journal_detail.id_currencyfx = db.app_currencyfx.Where(x => x.buy_value == COTIZACION).FirstOrDefault().id_currencyfx;
}
int ModuloID = Convert.ToInt16(chart_reader["ModuloID"]);
if (ModuloID == 3)
{
accounting_journal_detail.id_application = entity.App.Names.PurchaseInvoice;
}
else if (ModuloID == 4)
{
accounting_journal_detail.id_application = entity.App.Names.AccountsPayable;
}
else if (ModuloID == 8)
{
accounting_journal_detail.id_application = entity.App.Names.SalesInvoice;
}
else if (ModuloID == 26)
{
accounting_journal_detail.id_application = entity.App.Names.PaymentUtility;
}
accounting_journal_detail.is_head = true;
accounting_journal_detail.credit = Convert.ToDecimal(chart_reader["IMPORTEH"]);
accounting_journal_detail.debit = Convert.ToDecimal(chart_reader["IMPORTED"]);
accounting_journal_detail.accounting_journal = accounting_journal;
accounting_journal_detail.trans_date = accounting_journal.trans_date;
return(accounting_journal_detail);
}
public void btnSave_MouseUp(object sender, EventArgs e)
{
List <accounting_template_detail> accounting_template_detail;
int id_template = 0;
if (cbxTemplate.SelectedValue != null)
{
id_template = (int)cbxTemplate.SelectedValue;
}
accounting_template_detail = entity.db.accounting_template.Where(x => x.id_template == id_template).FirstOrDefault().accounting_template_detail.ToList();
accounting_journal _accounting_journal = new accounting_journal();
_accounting_journal.id_cycle = (int)cbxFiscal.SelectedValue;
_accounting_journal.comment = commentTextBox.Text;
foreach (accounting_template_detail _accounting_template_detail in accounting_template_detail)
{
accounting_journal_detail accounting_journal_detail = new accounting_journal_detail();
accounting_journal_detail.id_application = App.Names.Items;
accounting_journal_detail.id_chart = _accounting_template_detail.id_chart;
accounting_journal_detail.id_currencyfx = entity.db.app_currency.Where(x => x.is_priority == true).FirstOrDefault().app_currencyfx.FirstOrDefault().id_currencyfx;
if (_accounting_template_detail.is_debit == true)
{
accounting_journal_detail.debit = Convert.ToDecimal(txtAmount.Text) * _accounting_template_detail.coefficeint;
accounting_journal_detail.credit = 0;
}
else
{
accounting_journal_detail.debit = 0;
accounting_journal_detail.credit = Convert.ToDecimal(txtAmount.Text) * _accounting_template_detail.coefficeint;
}
accounting_journal_detail.trans_date = (DateTime)dtpTrans_Date.SelectedDate;
accounting_journal_detail.is_head = false;
_accounting_journal.accounting_journal_detail.Add(accounting_journal_detail);
accounting_journal = _accounting_journal;
// entity.db.accounting_journal.Add(accounting_journal);
}
Save_Click(sender);
}
private void UserControl_Loaded(object sender, RoutedEventArgs e)
{
accounting_journalViewSource = ((CollectionViewSource)(FindResource("accounting_journalViewSource")));
accounting_journal_detailViewSource = ((CollectionViewSource)(FindResource("accounting_journal_detailViewSource")));
if (accounting_journal != null)
{
db.accounting_journal.Where(x => x.id_journal == accounting_journal.id_journal).Load();
accounting_journalViewSource.Source = db.accounting_journal.Local;
//if (accounting_journal_detailDataGrid != null)
//{
// accounting_journal_detailDataGrid.ItemsSource = db.accounting_journal_detail.Where(x => x.id_journal == accounting_journal.id_journal).ToList();
//}
}
else
{
accounting_journal = new accounting_journal();
accounting_journal.id_cycle = db.accounting_cycle.Where(x => x.id_company == _setting.company_ID && x.is_active == true).FirstOrDefault().id_cycle;
db.accounting_journal.Add(accounting_journal);
accounting_journalViewSource.Source = db.accounting_journal.Local;
//if (accounting_journal_detailDataGrid != null)
//{
// accounting_journal_detailDataGrid.ItemsSource = accounting_journal.accounting_journal_detail;
//}
accounting_journalViewSource.View.MoveCurrentTo(accounting_journal);
}
CollectionViewSource accounting_chartViewSource = ((CollectionViewSource)(FindResource("accounting_chartViewSource")));
accounting_chartViewSource.Source = db.accounting_chart.Where(x => x.id_company == _setting.company_ID).ToList();
CollectionViewSource app_currencyfxViewSource = ((CollectionViewSource)(FindResource("app_currencyfxViewSource")));
List <app_currencyfx> app_currencyfxList = db.app_currencyfx.Where(x => x.id_company == _setting.company_ID && x.is_active &&
x.type == app_currencyfx.CurrencyFXTypes.Accounting).ToList();
app_currencyfxViewSource.Source = app_currencyfxList.Where(x => x.timestamp.Date == accounting_journal.trans_date.Date).ToList();
}
private accounting_journal Calculate_PurchaseInvoice(AccountingJournalDB AccountingJournalDB, purchase_invoice purchase_invoice, accounting_cycle accounting_cycle)
{
accounting_journal accounting_journal = new accounting_journal();
if (purchase_invoice.accounting_journal == null)
{
accounting_journal.id_cycle = accounting_cycle.id_cycle;
accounting_journal.trans_date = purchase_invoice.trans_date;
accounting_journal.IsSelected = true;
accounting_journal.State = EntityState.Added;
accounting_journal.comment = purchase_invoice.comment;
List <accounting_journal_detail> accounting_journal_detailList = new List <accounting_journal_detail>();
foreach (purchase_invoice_detail purchase_invoice_detail in purchase_invoice.purchase_invoice_detail.ToList())
{
if (purchase_invoice_detail.app_cost_center.is_product && purchase_invoice_detail.item != null)
{
List <item_tag_detail> item_tag_detailLIST = purchase_invoice_detail.item.item_tag_detail.ToList();
if (item_tag_detailLIST != null)
{
Asset.Inventory Inventory = new Asset.Inventory();
accounting_chart INV_Chart = null;
foreach (item_tag_detail item_tag_detail in purchase_invoice_detail.item.item_tag_detail.ToList())
{
item_tag item_tag = item_tag_detail.item_tag;
INV_Chart = Inventory.find_Chart(AccountingJournalDB, item_tag);
}
if (INV_Chart != null)
{
accounting_journal_detail INV_accounting_journal = new accounting_journal_detail();
INV_accounting_journal.accounting_chart = INV_Chart;
INV_accounting_journal.trans_date = purchase_invoice.trans_date;
INV_accounting_journal.debit = Math.Round(purchase_invoice_detail.SubTotal, 2);
INV_accounting_journal.id_currencyfx = purchase_invoice.app_currencyfx.id_currencyfx;
accounting_journal_detailList.Add(INV_accounting_journal);
}
}
}
else if (purchase_invoice_detail.app_cost_center.is_fixedasset)
{
//Ignore
}
else
{
Expense.AdministrationExpense AdministrationExpense = new Expense.AdministrationExpense();
app_cost_center app_cost_center = purchase_invoice_detail.app_cost_center;
accounting_chart Exp_Chart = AdministrationExpense.find_Chart(AccountingJournalDB, app_cost_center);
if (Exp_Chart != null)
{
accounting_journal_detail INV_accounting_journal = new accounting_journal_detail();
INV_accounting_journal.accounting_chart = Exp_Chart;
INV_accounting_journal.trans_date = purchase_invoice.trans_date;
INV_accounting_journal.debit = Math.Round(purchase_invoice_detail.SubTotal, 2);
INV_accounting_journal.id_currencyfx = purchase_invoice.app_currencyfx.id_currencyfx;
accounting_journal_detailList.Add(INV_accounting_journal);
}
}
}
Liability.ValueAddedTax VAT = new Liability.ValueAddedTax();
foreach (purchase_invoice_detail purchase_invoice_detail in purchase_invoice.purchase_invoice_detail.ToList())
{
foreach (app_vat_group_details app_vat_group in purchase_invoice_detail.app_vat_group.app_vat_group_details)
{
accounting_chart VAT_Chart = VAT.find_Chart(AccountingJournalDB, app_vat_group.app_vat);
if (VAT_Chart != null)
{
accounting_journal_detail INV_accounting_journal = new accounting_journal_detail();
INV_accounting_journal.accounting_chart = VAT_Chart;
INV_accounting_journal.trans_date = purchase_invoice.trans_date;
INV_accounting_journal.debit = Math.Round(Vat.calculate_Vat((purchase_invoice_detail.unit_cost * purchase_invoice_detail.quantity), app_vat_group.app_vat.coefficient), 2);
INV_accounting_journal.id_currencyfx = purchase_invoice.app_currencyfx.id_currencyfx;
accounting_journal_detailList.Add(INV_accounting_journal);
}
}
}
List <payment_schedual> payment_schedualLIST = AccountingJournalDB.payment_schedual.Where(x => x.id_purchase_invoice == purchase_invoice.id_purchase_invoice).ToList();
if (payment_schedualLIST != null)
{
///For Loop for each Payment Schedual.
foreach (payment_schedual payment_schedual in payment_schedualLIST)
{
///Example: 1000$ Invoice.
///Example: 600$ Paid & 400$ NotPaid.
///Payment Done -> Ex. 600$
if (payment_schedual.payment_detail != null && payment_schedual.debit > 0)
{
Asset.Cash CashAccount = new Asset.Cash();
accounting_chart AR_Chart = CashAccount.find_Chart(AccountingJournalDB, payment_schedual.payment_detail.app_account);
if (AR_Chart != null)
{
accounting_journal_detail PAYaccounting_journal_detail = new accounting_journal_detail();
PAYaccounting_journal_detail.accounting_chart = AR_Chart;
PAYaccounting_journal_detail.trans_date = payment_schedual.trans_date;
PAYaccounting_journal_detail.credit = payment_schedual.debit;
PAYaccounting_journal_detail.id_currencyfx = payment_schedual.app_currencyfx.id_currencyfx;
accounting_journal_detailList.Add(PAYaccounting_journal_detail);
}
}
///Payment Left -> Ex. 400$
else if (payment_schedual.payment_detail == null && payment_schedual.credit > 0)
{
//Credit Payment
Liability.AccountsPayable AccountsPayable = new Liability.AccountsPayable();
accounting_chart AR_Chart = AccountsPayable.find_Chart(AccountingJournalDB, purchase_invoice.contact);
if (AR_Chart != null)
{
accounting_journal_detail AR_accounting_journal_detail = new accounting_journal_detail();
AR_accounting_journal_detail.accounting_chart = AR_Chart;
AR_accounting_journal_detail.trans_date = purchase_invoice.trans_date;
AR_accounting_journal_detail.credit = payment_schedual.credit - payment_schedual.child.Sum(x => x.debit);
AR_accounting_journal_detail.id_currencyfx = purchase_invoice.app_currencyfx.id_currencyfx;
accounting_journal_detailList.Add(AR_accounting_journal_detail);
}
}
}
}
///Clean up Duplicate Accounts.
///If Duplicate, will sum into first of the same chart it Finds.
foreach (accounting_journal_detail accounting_journal_detail in accounting_journal_detailList)
{
int id_chart = accounting_journal_detail.accounting_chart.id_chart;
if (accounting_journal.accounting_journal_detail.Where(x => x.id_chart == id_chart).Count() == 0)
{
accounting_journal_detail PAYaccounting_journal_detail = new accounting_journal_detail();
PAYaccounting_journal_detail.id_chart = accounting_journal_detail.accounting_chart.id_chart;
PAYaccounting_journal_detail.accounting_chart = accounting_journal_detail.accounting_chart;
PAYaccounting_journal_detail.trans_date = accounting_journal_detail.trans_date;
PAYaccounting_journal_detail.credit = Math.Round(accounting_journal_detail.credit, 2);
PAYaccounting_journal_detail.debit = Math.Round(accounting_journal_detail.debit, 2);
PAYaccounting_journal_detail.id_currencyfx = accounting_journal_detail.id_currencyfx;
accounting_journal.accounting_journal_detail.Add(PAYaccounting_journal_detail);
}
else
{
accounting_journal_detail PAYaccounting_journal_detail = accounting_journal.accounting_journal_detail.Where(x => x.id_chart == id_chart).FirstOrDefault();
PAYaccounting_journal_detail.credit += Math.Round(accounting_journal_detail.credit, 2);
PAYaccounting_journal_detail.debit += Math.Round(accounting_journal_detail.debit, 2);
}
}
accounting_journal.purchase_invoice.Add(purchase_invoice);
}
return(accounting_journal);
}
public void accounting()
{
SqlConnection conn = new SqlConnection(_connString);
SqlCommand cmd = new SqlCommand();
string sql = " SELECT plancuentas.NUMPLANCUENTA, plancuentas.DESPLANCUENTA, plancuentas.TIPOCUENTA, plancuentas.NIVELCUENTA, plancuentas.ASENTABLE, plancuentas.DESTIPOCUENTA, plancuentas.REGLA, plancuentas.REGLAFK, CENTROCOSTO.DESCENTRO, CENTROCOSTO.ENLAZADO, CLIENTES.NOMBRE AS CUSTOMER, "
+ " PROVEEDOR.NOMBRE AS SUPPLIER, CAJA.NUMEROCAJA, IVA.DESIVA"
+ " FROM IVA RIGHT OUTER JOIN"
+ " plancuentas ON IVA.CODIVA = plancuentas.REGLAFK LEFT OUTER JOIN"
+ " CAJA ON plancuentas.REGLAFK = CAJA.NUMCAJA LEFT OUTER JOIN"
+ " PROVEEDOR ON plancuentas.REGLAFK = PROVEEDOR.CODPROVEEDOR LEFT OUTER JOIN"
+ " CLIENTES ON plancuentas.REGLAFK = CLIENTES.CODCLIENTE LEFT OUTER JOIN"
+ " CENTROCOSTO ON plancuentas.REGLAFK = CENTROCOSTO.CODCENTRO"
+ " ORDER BY plancuentas.NUMPLANCUENTA";
conn.Open();
cmd.Connection = conn;
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
DataTable dt = exeDT(sql);
// SqlDataReader chart_reader = cmd.ExecuteReader();
Dispatcher.BeginInvoke((Action)(() => progAccounting.IsIndeterminate = true));
foreach (DataRow chart_reader in dt.Rows)
{
using (db db = new db())
{
db.Configuration.AutoDetectChangesEnabled = false;
accounting_chart accounting_chart = new accounting_chart();
accounting_chart.id_user = db.security_user.Where(x => x.id_company == id_company).FirstOrDefault().id_user;
accounting_chart.id_company = id_company;
accounting_chart.is_active = true;
if ((chart_reader["DESPLANCUENTA"] is DBNull))
{
continue;
}
else
{
accounting_chart.name = (string)chart_reader["DESPLANCUENTA"];
}
if ((chart_reader["NUMPLANCUENTA"] is DBNull))
{
continue;
}
else
{
accounting_chart.code = (string)chart_reader["NUMPLANCUENTA"];
}
//Find Parent Account
string code = accounting_chart.code;
var pos = code.LastIndexOf('.');
if (pos >= 0)
{
code = accounting_chart.code.Substring(0, pos);
accounting_chart.parent = db.accounting_chart.Where(p => p.code == code &&
p.id_account == accounting_chart.id_account
).FirstOrDefault();
}
//Tipo de Cuenta
if (!(chart_reader[2] is DBNull))
{
if (Convert.ToInt16(chart_reader["TIPOCUENTA"]) == 1)
{
accounting_chart.chart_type = accounting_chart.ChartType.Assets;
}
else if (Convert.ToInt16(chart_reader["TIPOCUENTA"]) == 2)
{
accounting_chart.chart_type = accounting_chart.ChartType.Liability;
}
else if (Convert.ToInt16(chart_reader["TIPOCUENTA"]) == 3)
{
accounting_chart.chart_type = accounting_chart.ChartType.Equity;
}
else if (Convert.ToInt16(chart_reader["TIPOCUENTA"]) == 4)
{
accounting_chart.chart_type = accounting_chart.ChartType.Revenue;
}
else if (Convert.ToInt16(chart_reader["TIPOCUENTA"]) == 5)
{
accounting_chart.chart_type = accounting_chart.ChartType.Expenses;
}
}
accounting_chart.is_generic = true;
// Linking
if (chart_reader[4].ToString() == "Si")
{
if (!(chart_reader["REGLA"] is DBNull))
{
if (Convert.ToInt16(chart_reader["REGLA"]) == 1)
{
}
else if (Convert.ToInt16(chart_reader["REGLA"]) == 2)
{
//Fix issue.
}
//ACCOUNTS RECEIVABLE
else if (Convert.ToInt16(chart_reader["REGLA"]) == 3)
{
accounting_chart.chartsub_type = accounting_chart.ChartSubType.AccountsReceivable;
string _supplier = Convert.ToString(chart_reader["SUPPLIER"]);
if (Convert.ToInt16(chart_reader["REGLAFK"]) != 0 && _supplier != null)
{
accounting_chart.contact = db.contacts.Where(c => c.name == _supplier).FirstOrDefault();
accounting_chart.is_generic = false;
}
}
//ACCOUNTS PAYABLE
else if (Convert.ToInt16(chart_reader["REGLA"]) == 4)
{
accounting_chart.chartsub_type = accounting_chart.ChartSubType.AccountsPayable;
string _customer = Convert.ToString(chart_reader["CUSTOMER"]);
if (Convert.ToInt16(chart_reader["REGLAFK"]) != 0 && _customer != null)
{
accounting_chart.contact = db.contacts.Where(c => c.name == _customer).FirstOrDefault();
accounting_chart.is_generic = false;
}
}
//VALUE ADDED TAX
else if (Convert.ToInt16(chart_reader["REGLA"]) == 5)
{
accounting_chart.chartsub_type = accounting_chart.ChartSubType.VAT;
string _iva = Convert.ToString(chart_reader["DESIVA"]);
if (Convert.ToInt16(chart_reader["REGLAFK"]) != 0 && _iva != null)
{
accounting_chart.app_vat = db.app_vat.Where(c => c.name == _iva).FirstOrDefault();
accounting_chart.is_generic = false;
}
}
//VALUE ADDED TAX
else if (Convert.ToInt16(chart_reader["REGLA"]) == 6)
{
accounting_chart.chartsub_type = accounting_chart.ChartSubType.Revenue;
}
else if (Convert.ToInt16(chart_reader["REGLA"]) == 7)
{
}
//CASH ACCOUNTS
else if (Convert.ToInt16(chart_reader["REGLA"]) == 8)
{
accounting_chart.chartsub_type = accounting_chart.ChartSubType.Cash;
string _account_name = Convert.ToString(chart_reader["NUMEROCAJA"]);
if (Convert.ToInt16(chart_reader["REGLAFK"]) != 0 && _account_name != null)
{
accounting_chart.app_account = db.app_account.Where(c => c.name == _account_name).FirstOrDefault();
accounting_chart.is_generic = false;
}
}
else if (Convert.ToInt16(chart_reader["REGLA"]) == 9)
{
}
else if (Convert.ToInt16(chart_reader["REGLA"]) == 10)
{
}
//COST CENTER
else if (Convert.ToInt16(chart_reader["REGLA"]) == 11)
{
if (Convert.ToString(chart_reader["ENLAZADO"]) != null)
{
//INVENTORY COST CENTER
accounting_chart.chartsub_type = accounting_chart.ChartSubType.Inventory;
}
else
{
//EXPENSE COST CENTER
accounting_chart.chartsub_type = accounting_chart.ChartSubType.AdministrationExpense;
//add small logic to seperate stockable from expense.
string _cost_center = Convert.ToString(chart_reader["DESCENTRO"]);
if (Convert.ToInt16(chart_reader["REGLAFK"]) != 0 && _cost_center != null)
{
accounting_chart.app_cost_center = db.app_cost_center.Where(c => c.name == _cost_center).FirstOrDefault();
accounting_chart.is_generic = false;
}
}
}
}
}
if (accounting_chart.Error == null)
{
db.accounting_chart.Add(accounting_chart);
try
{
db.SaveChanges();
}
catch
{
throw;
}
}
}
}
//chart_reader.Close();
dt.Clear();
cmd.Dispose();
conn.Close();
string sql_periodofiscal = " SELECT * from periodofiscal ";
conn.Open();
cmd.Connection = conn;
cmd.CommandText = sql_periodofiscal;
cmd.CommandType = CommandType.Text;
SqlDataReader periodofiscal = cmd.ExecuteReader();
while (periodofiscal.Read())
{
using (db db = new db())
{
accounting_cycle accounting_cycle = new accounting_cycle();
accounting_cycle.id_user = db.security_user.Where(x => x.id_company == id_company).FirstOrDefault().id_user;
accounting_cycle.start_date = Convert.ToDateTime(periodofiscal["FECHAINICIO"]);
accounting_cycle.end_date = Convert.ToDateTime(periodofiscal["FECHAFIN"]);
accounting_cycle.name = Convert.ToString(periodofiscal["DESEJERCICIO"]);
accounting_cycle.id_cycle = Convert.ToInt32(periodofiscal["CODPERIODOFISCAL"]);
if (periodofiscal["ESTADO"] is DBNull)
{
accounting_cycle.is_active = false;
}
else
{
accounting_cycle.is_active = Convert.ToBoolean(periodofiscal["ESTADO"]);
}
db.accounting_cycle.Add(accounting_cycle);
try
{
IEnumerable <DbEntityValidationResult> validationresult = dbContext.GetValidationErrors();
if (validationresult.Count() == 0)
{
db.SaveChanges();
}
}
catch
{
throw;
}
}
}
periodofiscal.Close();
//cmd.Dispose();
conn.Close();
Dispatcher.BeginInvoke((Action)(() => progAccounting.IsIndeterminate = false));
string sql_journal = " SELECT plancuentas.DESPLANCUENTA,periodofiscal.CODPERIODOFISCAL, periodofiscal.DESEJERCICIO, ASIENTOS.CODMONEDA,ASIENTOS.ModuloID, ASIENTOS.NUMASIENTO, ASIENTOS.FECHAASIENTO, ASIENTOS.COTIZACION, ASIENTOS.DESCRIPCION, ASIENTOS.IMPORTED, ASIENTOS.IMPORTEH, ASIENTOS.NUMCOMPROBANTE, ASIENTOS.DETALLE, ASIENTOS.TIMBRADO, SUCURSAL.DESSUCURSAL"
+ " FROM ASIENTOS INNER JOIN"
+ " plancuentas ON ASIENTOS.CODPLANCUENTA = plancuentas.CODPLANCUENTA INNER JOIN"
+ " periodofiscal ON ASIENTOS.CODPERIODOFISCAL = periodofiscal.CODPERIODOFISCAL INNER JOIN"
+ " SUCURSAL ON ASIENTOS.CODSUCURSAL = SUCURSAL.CODSUCURSAL";
SqlDataReader chart_journal;
try
{
conn.Open();
cmd.Connection = conn;
cmd.CommandText = sql_journal;
cmd.CommandType = CommandType.Text;
chart_journal = cmd.ExecuteReader();
DataTable dtjournal = exeDT(sql_journal);
int count = dtjournal.Rows.Count;
int value = 0;
Dispatcher.BeginInvoke((Action)(() => accountingMaximum.Text = count.ToString()));
Dispatcher.BeginInvoke((Action)(() => accountingValue.Text = value.ToString()));
Dispatcher.BeginInvoke((Action)(() => progAccounting.Maximum = count));
Dispatcher.BeginInvoke((Action)(() => progAccounting.Value = value));
while (chart_journal.Read())
{
using (db db = new db())
{
db.Configuration.AutoDetectChangesEnabled = false;
accounting_journal accounting_journal;
int _code = 0;
if (!(chart_journal["NUMASIENTO"] is DBNull))
{
_code = Convert.ToInt16(chart_journal["NUMASIENTO"]);
}
int id = Convert.ToInt32(chart_journal["CODPERIODOFISCAL"]);
int id_cycle = 0;
if (db.accounting_cycle.Where(x => x.id_cycle == id).FirstOrDefault() != null)
{
id_cycle = Convert.ToInt32(chart_journal["CODPERIODOFISCAL"]);
}
else
{
id_cycle = db.accounting_cycle.FirstOrDefault().id_cycle;
}
try
{
if (db.accounting_journal.Where(i => i.code == _code && i.id_company == id_company && i.id_cycle == id_cycle).Count() == 0)
{
accounting_journal = new accounting_journal();
accounting_journal.id_cycle = id_cycle;
accounting_journal.code = _code;
accounting_journal.id_company = id_company;
string branch_name = Convert.ToString(chart_journal["DESSUCURSAL"]);
accounting_journal.app_branch = db.app_branch.Where(i => i.name == branch_name && i.id_company == id_company).FirstOrDefault();
accounting_journal.trans_date = Convert.ToDateTime(chart_journal["FECHAASIENTO"]);
accounting_journal.comment = Convert.ToString(chart_journal["DESCRIPCION"]);
accounting_journal_detail accounting_journal_detail = create_Detail(ref accounting_journal, chart_journal);
if (accounting_journal_detail != null)
{
accounting_journal.accounting_journal_detail.Add(accounting_journal_detail);
}
db.accounting_journal.Add(accounting_journal);
}
else
{
accounting_journal = db.accounting_journal.Where(i => i.code == _code && i.id_cycle == id_cycle).FirstOrDefault();
accounting_journal.trans_date = Convert.ToDateTime(chart_journal["FECHAASIENTO"]);
accounting_journal_detail accounting_journal_detail = create_Detail(ref accounting_journal, chart_journal);
if (accounting_journal_detail != null)
{
db.accounting_journal_detail.Add(accounting_journal_detail);
}
}
try
{
IEnumerable <DbEntityValidationResult> validationresult = db.GetValidationErrors();
if (validationresult.Count() == 0)
{
db.SaveChanges();
value += 1;
Dispatcher.BeginInvoke((Action)(() => progAccounting.Value = value));
Dispatcher.BeginInvoke((Action)(() => accountingValue.Text = value.ToString()));
}
}
catch
{
throw;
}
}
catch
{
throw;
}
}
}
}
catch
{
throw;
}
Dispatcher.BeginInvoke((Action)(() => progAccounting.IsIndeterminate = false));
}
private accounting_journal calc(AccountingJournalDB context, sales_invoice sales_invoice, accounting_cycle accounting_cycle)
{
//List<accounting_journal> ListAccountingJournal = new List<accounting_journal>();
accounting_journal accounting_journal = new accounting_journal();
if (sales_invoice.accounting_journal == null)
{
// accounting_journal accounting_journal = new accounting_journal();
accounting_journal.id_cycle = accounting_cycle.id_cycle;
accounting_journal.comment = sales_invoice.comment;
accounting_journal.trans_date = sales_invoice.trans_date;
accounting_journal.State = EntityState.Added;
List <accounting_journal_detail> accounting_journal_detailList = new List <accounting_journal_detail>();
//List<sales_invoice_detail> _sales_invoice_detail = sales_invoice.sales_invoice_detail.ToList();
Asset.Inventory Inventory = new Asset.Inventory();
foreach (sales_invoice_detail sales_invoice_detail in sales_invoice.sales_invoice_detail.ToList())
{
accounting_chart INV_Chart = null;
if (sales_invoice_detail.item != null)
{
if (sales_invoice_detail.item.item_tag_detail != null)
{
foreach (item_tag_detail item_tag_detail in sales_invoice_detail.item.item_tag_detail.ToList())
{
item_tag item_tag = item_tag_detail.item_tag;
if (Inventory.find_Chart(context, item_tag) != null)
{
INV_Chart = Inventory.find_Chart(context, item_tag);
}
}
}
}
else
{
INV_Chart = Inventory.find_Chart(context, null);
}
if (INV_Chart != null)
{
accounting_journal_detail INV_accounting_journal = new accounting_journal_detail();
INV_accounting_journal.accounting_chart = INV_Chart;
if (context.app_currencyfx.Where(x => x.type == app_currencyfx.CurrencyFXTypes.Accounting &&
x.id_currency == sales_invoice.app_currencyfx.id_currency && x.timestamp <= DateTime.Now)
.OrderByDescending(x => x.timestamp).FirstOrDefault() != null)
{
INV_accounting_journal.id_currencyfx = context.app_currencyfx.Where(x => x.type == app_currencyfx.CurrencyFXTypes.Accounting &&
x.id_currency == sales_invoice.app_currencyfx.id_currency && x.timestamp <= DateTime.Now)
.OrderByDescending(x => x.timestamp).FirstOrDefault().id_currencyfx;
}
else
{
INV_accounting_journal.id_currencyfx = sales_invoice_detail.sales_invoice.id_currencyfx;
}
INV_accounting_journal.credit = sales_invoice_detail.SubTotal;
INV_accounting_journal.trans_date = sales_invoice.trans_date;
accounting_journal_detailList.Add(INV_accounting_journal);
}
}
foreach (sales_invoice_detail sales_invoice_detail in sales_invoice.sales_invoice_detail.ToList())
{
foreach (app_vat_group_details app_vat_group_details in sales_invoice_detail.app_vat_group.app_vat_group_details)
{
Asset.ValueAddedTax VAT = new Asset.ValueAddedTax();
accounting_chart VAT_Chart = VAT.find_Chart(context, app_vat_group_details.app_vat);
if (VAT_Chart != null)
{
accounting_journal_detail VAT_accounting_journal = new accounting_journal_detail();
VAT_accounting_journal.trans_date = sales_invoice.trans_date;
VAT_accounting_journal.accounting_chart = VAT_Chart;
VAT_accounting_journal.credit = Vat.calculate_Vat((sales_invoice_detail.unit_price * sales_invoice_detail.quantity), app_vat_group_details.app_vat.coefficient);
if (context.app_currencyfx.Where(x => x.type == app_currencyfx.CurrencyFXTypes.Accounting &&
x.id_currency == sales_invoice.app_currencyfx.id_currency && x.timestamp <= DateTime.Now)
.OrderByDescending(x => x.timestamp).FirstOrDefault() != null)
{
VAT_accounting_journal.id_currencyfx = context.app_currencyfx.Where(x => x.type == app_currencyfx.CurrencyFXTypes.Accounting &&
x.id_currency == sales_invoice.app_currencyfx.id_currency && x.timestamp <= DateTime.Now)
.OrderByDescending(x => x.timestamp).FirstOrDefault().id_currencyfx;
}
else
{
VAT_accounting_journal.id_currencyfx = sales_invoice_detail.sales_invoice.id_currencyfx;
}
accounting_journal_detailList.Add(VAT_accounting_journal);
}
}
}
List <payment_schedual> payment_schedualLIST = context.payment_schedual.Where(x => x.id_sales_invoice == sales_invoice.id_sales_invoice).ToList();
if (payment_schedualLIST != null)
{
///For Loop for each Payment Schedual.
foreach (payment_schedual payment_schedual in payment_schedualLIST)
{
///Example: 1000$ Invoice.
///Example: 600$ Paid & 400$ NotPaid.
///Payment Done -> Ex. 600$
if (payment_schedual.payment_detail != null && payment_schedual.credit > 0)
{
Asset.Cash CashAccount = new Asset.Cash();
accounting_chart AR_Chart = CashAccount.find_Chart(context, payment_schedual.payment_detail.app_account);
if (AR_Chart != null)
{
accounting_journal_detail PAYaccounting_journal_detail = new accounting_journal_detail();
PAYaccounting_journal_detail.accounting_chart = AR_Chart;
PAYaccounting_journal_detail.trans_date = payment_schedual.trans_date;
PAYaccounting_journal_detail.debit = payment_schedual.credit;
if (context.app_currencyfx.Where(x => x.type == app_currencyfx.CurrencyFXTypes.Accounting &&
x.id_currency == payment_schedual.app_currencyfx.id_currency && x.timestamp <= DateTime.Now)
.OrderByDescending(x => x.timestamp).FirstOrDefault() != null)
{
PAYaccounting_journal_detail.id_currencyfx = context.app_currencyfx.Where(x => x.type == app_currencyfx.CurrencyFXTypes.Accounting &&
x.id_currencyfx == payment_schedual.id_currencyfx && x.timestamp <= DateTime.Now)
.OrderByDescending(x => x.timestamp).FirstOrDefault().id_currencyfx;
}
else
{
PAYaccounting_journal_detail.id_currencyfx = payment_schedual.id_currencyfx;
}
accounting_journal_detailList.Add(PAYaccounting_journal_detail);
}
}
///Payment Left -> Ex. 400$
else if (payment_schedual.payment_detail == null && payment_schedual.debit > 0)
{
//Credit Payment
Asset.AccountsReceivable AccountsReceivable = new Asset.AccountsReceivable();
accounting_chart AR_Chart = AccountsReceivable.find_Chart(context, sales_invoice.contact);
if (AR_Chart != null)
{
accounting_journal_detail AR_accounting_journal_detail = new accounting_journal_detail();
AR_accounting_journal_detail.accounting_chart = AR_Chart;
AR_accounting_journal_detail.trans_date = sales_invoice.trans_date;
AR_accounting_journal_detail.debit = payment_schedual.debit - payment_schedual.child.Sum(x => x.credit);
if (context.app_currencyfx.Where(x => x.type == app_currencyfx.CurrencyFXTypes.Accounting &&
x.id_currency == payment_schedual.app_currencyfx.id_currency && x.timestamp <= DateTime.Now)
.OrderByDescending(x => x.timestamp).FirstOrDefault() != null)
{
AR_accounting_journal_detail.id_currencyfx = context.app_currencyfx.Where(x => x.type == app_currencyfx.CurrencyFXTypes.Accounting &&
x.id_currency == payment_schedual.app_currencyfx.id_currency && x.timestamp <= DateTime.Now)
.OrderByDescending(x => x.timestamp).FirstOrDefault().id_currencyfx;
}
else
{
AR_accounting_journal_detail.id_currencyfx = payment_schedual.id_currencyfx;
}
accounting_journal_detailList.Add(AR_accounting_journal_detail);
}
}
}
}
///SUMMARIZE
foreach (accounting_journal_detail accounting_journal_detail in accounting_journal_detailList)
{
int id_chart = accounting_journal_detail.accounting_chart.id_chart;
if (accounting_journal.accounting_journal_detail.Where(x => x.id_chart == id_chart).Count() == 0)
{
accounting_journal_detail PAYaccounting_journal_detail = new accounting_journal_detail();
PAYaccounting_journal_detail.id_chart = accounting_journal_detail.accounting_chart.id_chart;
PAYaccounting_journal_detail.accounting_chart = accounting_journal_detail.accounting_chart;
PAYaccounting_journal_detail.trans_date = accounting_journal_detail.trans_date;
PAYaccounting_journal_detail.credit = accounting_journal_detail.credit;
PAYaccounting_journal_detail.debit = accounting_journal_detail.debit;
if (context.app_currencyfx.Where(x => x.type == app_currencyfx.CurrencyFXTypes.Accounting &&
x.id_currency == accounting_journal_detail.app_currencyfx.id_currency && x.timestamp <= DateTime.Now)
.OrderByDescending(x => x.timestamp).FirstOrDefault() != null)
{
PAYaccounting_journal_detail.id_currencyfx = context.app_currencyfx.Where(x => x.type == app_currencyfx.CurrencyFXTypes.Accounting &&
x.id_currency == accounting_journal_detail.app_currencyfx.id_currency && x.timestamp <= DateTime.Now)
.OrderByDescending(x => x.timestamp).FirstOrDefault().id_currencyfx;
}
else
{
PAYaccounting_journal_detail.id_currencyfx = accounting_journal_detail.id_currencyfx;
}
accounting_journal.accounting_journal_detail.Add(PAYaccounting_journal_detail);
}
else
{
accounting_journal_detail PAYaccounting_journal_detail = accounting_journal.accounting_journal_detail.Where(x => x.id_chart == id_chart).FirstOrDefault();
PAYaccounting_journal_detail.credit += accounting_journal_detail.credit;
PAYaccounting_journal_detail.debit += accounting_journal_detail.debit;
}
}
accounting_journal.sales_invoice.Add(sales_invoice);
}
return(accounting_journal);
}