private void Items()
{
AccessQueries accquer = new AccessQueries();
DataTable dt1 = new DataTable();
DateTime itemdate = DateTime.Now.AddYears(-3);
string vendorid = "", invoiceid = "", invoiceitemid = "", itemid = "", quantityid = "", departmentid = "";
int units = 0;
string invoicenum = "";
string vendornumber = "";
decimal cost = 0;
decimal retail = 0;
if (dpItemDate.Text != null || dpItemDate.Text != "")
{
itemdate = conv.ConvertToDate(dpItemDate.Text, -1095);
} // TODO: Validate if it is an actual date. Change to DatePicker.
// 1 = Paragould, 2 = Jackson, 4 = Jonesboro
if (tbDeptNum.Text == "1" || tbDeptNum.Text == "2" || tbDeptNum.Text == "4")
{
dt1 = accquer.GetItems(tbDeptNum.Text, itemdate);
}
else { dt1 = accquer.GetItems("1", itemdate); } // Query Access with parameter of a store number and cutoff date.
if (dt1.Rows.Count > 0)
{
foreach (DataRow row in dt1.Rows)
{
vendornumber = row[15].ToString();
if (row[9] == null) { cost = 0; } else {
cost = conv.ConvertToDecimal(row[9].ToString());
}
if (row[10] == null) { retail = 0; } // row 10 to Retail
else {
retail = conv.ConvertToDecimal(row[10].ToString());
}
if (row[11] == null) { units = 1; } else {
units = conv.ConvertToInt(row[11].ToString());
}
try { vendorid = ss.GetVendorID(vendornumber); }
catch (Exception) { logging.writeToLog("Warning: Unable to retrieve VendorID = " + vendornumber + " from ImportItems. "); }
if (vendorid == "" || vendorid == null) { vendorid = "1"; }
try { invoiceid = ss.GetInvoiceIDforImports(vendorid); }
catch (Exception) { logging.writeToLog("Warning: Unable to retrieve InvoiceID = " + vendorid.ToString() + " from ImportItems."); }
if (invoiceid == "" || invoiceid == null) {
invoicenum = "X" + vendornumber + "X";
invoiceid = si.InsertInvoices(invoicenum, row[13].ToString(), row[14].ToString(), vendorid, "22331111-0111-1000-1000-000011112222", "Created by System", "none"); // Invoices
}
itemid = si.InsertItems(row[0].ToString(), "44441111-0111-1000-1000-000011112222", row[8].ToString(), row[2].ToString(), cost, retail, 0, 0, 0, "", "Active", row[0].ToString(), row[1].ToString(), row[4].ToString(), row[16].ToString(), row[3].ToString(), "", "", row[5].ToString(), row[6].ToString(), row[7].ToString()); // Items
DataTable dt2 = ss.GetDepartmentByDeptNum(tbDeptNum.Text);
try
{
departmentid = dt2.Rows[0].Field<string>("id");
}
catch (Exception)
{
departmentid = "11111111-0111-1000-1000-000011112222";
}
// TODO: Add this to a list and search the list for quicker results.
// Find the date of the item and retrieve the invoiceid
// Insert invoice with datetime
invoiceitemid = si.InsertInvoiceItems(invoiceid, itemid, "Created by System", "", 0, 0, 0, 0, 0, departmentid, 0); // Invoice Items
quantityid = si.InsertItemQuantities(itemid, departmentid, units, "Inserted by System"); // Insert into ItemQuantities
}
try { dt1.Clear(); }
catch (Exception) { logging.writeToLog("Error: Clearing Results in Items DataTable"); }
}
PopUp pop = new PopUp("Items Imported.", "Items have finished importing.");
pop.Show();
}
private void btnDeltaSync_Click(object sender, RoutedEventArgs e)
{
AccessQueries accquer = new AccessQueries();
//txtDBSyncStatus.Text = "";
DateTime end = DateTime.Now; DateTime start = new DateTime();
string gac = "", writedate = end.ToString("MM/dd/yyyy"), storenum = "";
string query = "SELECT TOP 1 * FROM app_config WHERE ukey = 'previous_sync'";
if (ss.IfExists(query))
{ // if previuos_sync exists in database
gac = ss.GetAppConfig("previous_sync");
}
else {
si.InsertAppConfig("previous_sync", writedate);
gac = "02/01/2016";
}
// converts string gac to DateTime start
start = conv.ConvertToDate(gac, -7);
if (cbStores.IsChecked.Value == true)
{
}
if (cbEmployees.IsChecked.Value == true)
{
}
if (cbCustomers.IsChecked.Value == true)
{
DataTable customers = new DataTable();
DataTable chargecustomers = new DataTable();
// TODO: Get Acct Balances to show up
// Access Query => Field<decimal>("ACCTBAL")
// Iterate throught he list and do the following:
for (var dte = start; dte <= end; dte = dte.AddDays(1))
{
// Check to see if customer exists in the table
// Import Customers and ChargeCustomers by joindate from previous_import -> now.
customers = accquer.ImportCustomers(dte.ToString());
foreach (DataRow row in customers.Rows)
{
}
customers.Clear();
}
}
if (cbVendors.IsChecked.Value == true)
{
}
if (cbCategories.IsChecked.Value == true)
{
}
if (cbItems.IsChecked.Value == true)
{
DataTable items = new DataTable();
// Import items
for (var dt = start; dt <= end; dt = dt.AddDays(1))
{
items = accquer.GetItems(storenum, dt);
foreach (DataRow row in items.Rows)
{
// look for new items where date received is today
// input the information into the database
}
items.Clear();
}
}
if (cbTickets.IsChecked.Value == true) // Import tickets from date
{
DataTable tickets = new DataTable();
for (var dt = start; dt <= end; dt = dt.AddDays(1))
{
tickets = accquer.GetTicketsByDate(dt.ToString("MM/dd/yyyy"));
SyncTickets(tickets, dt.Date);
tickets.Clear();
}
}
su.UpdateAppConfig("previous_sync", writedate);
}
private void btnFullSync_Click(object sender, RoutedEventArgs e)
{
AccessQueries accquer = new AccessQueries();
//txtDBSyncStatus.Text = "";
if (cbStores.IsChecked.Value == true)
{
DataTable dt = new DataTable();
accquer.ImportDepartments();
//txtDBSyncStatus.Text += "Stores Imported...\n";
string addressid = "", departmentid = "";
foreach (DataRow row in dt.Rows)
{
// Insert into Address get ID
addressid = si.InsertAddress("", "", "", "", "", "", "USA", "Imported from Access");
// Insert into Person get ID
departmentid = si.InsertDepartment(row[0].ToString(), row[1].ToString(), addressid, "Imported from Access", "Active");
}
try { dt.Clear(); }
catch (Exception) {
logging.writeToLog("Error: Clearing the results in Department of dataTable. ");
}
}
if (cbEmployees.IsChecked.Value == true)
{
accquer.ImportEmployees(); // Import Employees
//txtDBSyncStatus.Text += "Employees Imported...\n";
}
if (cbCustomers.IsChecked.Value == true)
{
DataTable dt = new DataTable();
dt = accquer.ImportCustomers();
Customers(dt);
//txtDBSyncStatus.Text += "Customers Imported...\n";
}
if (cbVendors.IsChecked.Value == true)
{
DataTable dt = new DataTable();
eargs = eargs + "vendors ";
//txtDBSyncStatus.Text += "Vendors Imported...\n"; // Import Vendors
dt = accquer.ImportVendors();
VendorImport(dt);
}
if (cbCategories.IsChecked.Value == true)
{
//txtDBSyncStatus.Text += "Seasons Imported...\n";
//txtDBSyncStatus.Text += "Sizes Imported...\n";
//txtDBSyncStatus.Text += "Divisions Imported...\n"; // Import Divisions
//txtDBSyncStatus.Text += "Classes Imported...\n"; // Import Classes
// TODO: import categories
}
if (cbItems.IsChecked.Value == true) // Import Items
{
eargs = eargs + "items ";
Items();
}
if (cbTickets.IsChecked.Value == true)
{
DataTable tickets = new DataTable(); // Tickets => Date | TicketNumber | Total | Payment | Customer | Employee
string date = dpTickets.SelectedDate.ToString();
DateTime start = DateTime.Now; DateTime end = DateTime.Now;
start = conv.ConvertToDate(date);
if (start.ToString("MM/dd/yyyy") == DateTime.Now.ToString("MM/dd/yyyy"))
{
start = start.AddMonths(-4);
}
for (var dte = start; dte <= end; dte = dte.AddDays(1))
{
// Insert into tickets, ticket_items and ticket_payments
tickets = accquer.GetTicketsByDate(dte.ToString("MM/dd/yyyy"));
Tickets(tickets);
tickets.Clear();
}
PopUp pop = new PopUp("Tickets Imported.", "Tickets have finished importing.");
pop.Show();
}
//txtDBSyncStatus.Text += "Approvals Imported...\n";
//// Import Approvals
//txtDBSyncStatus.Text += "Charge Customers Imported...\n";
//// Import Charge Customers.
//txtDBSyncStatus.Text += "Cost Codes Imported...\n";
//// Import CostCode
//txtDBSyncStatus.Text += "Credits Imported...\n";
//// Import Credits
//txtDBSyncStatus.Text += "Orders Imported...\n";
//// Import Orders
//txtDBSyncStatus.Text += "Sales Imported...\n";
//// Import Sales
//txtDBSyncStatus.Text += "Tickets Imported...\n";
//// Import Tickets
}