public int PumpPayrollItems(string Company) { string sql = "SELECT * FROM PayrollItemWage WHERE TimeModified > " + CommonProcs.TimeStampString(DateTime.Now.AddDays(-1)); UpdateStatusBar("Opening Quickbooks", 10); List <QBPayrollItem> payItems = new ODBCReaderToModel <QBPayrollItem>().CreateList(sql, Company); if (payItems.Count > 0) { foreach (var QBpayItem in payItems) { if (QBpayItem.Name.Contains("00") || QBpayItem.Name.Contains("01") || QBpayItem.Name.Contains("02")) { UpdateStatusBar("Adding " + QBpayItem.Name, 1); PayrollItem payItem = QBpayItem.ConvertTo <PayrollItem>(); payItem.QBFile = Company; using (clsDataGetter dg = new clsDataGetter(CommonProcs.WCompanyConnStr)) { payItem.payItemID = dg.GetScalarInteger("SELECT payItemID FROM PayrollItem WHERE ListID='" + payItem.ListID + "' AND QBFile='" + Company + "'"); } new ModelToSQL <PayrollItem>().WriteUpdateToSQL("PayrollItem", payItem, "payItemID", CommonProcs.WCompanyConnStr); } } } UpdateStatusBar("Done", progBar.Maximum); return(payItems.Count); }
public int PumpEmployees(string Company) { string sql = "SELECT * FROM Employee WHERE TimeModified > " + CommonProcs.TimeStampString(DateTime.Now.AddDays(-1)); UpdateStatusBar("Opening Quickbooks", 10); List <QBEmployee> employees = new ODBCReaderToModel <QBEmployee>().CreateList(sql, Company); if (employees.Count > 0) { foreach (var QBemp in employees) { UpdateStatusBar("Adding " + QBemp.Name, 1); Employee emp = QBemp.ConvertTo <Employee>(); emp.QBFile = Company; using (clsDataGetter dg = new clsDataGetter(CommonProcs.WCompanyConnStr)) { emp.EmployeeID = dg.GetScalarInteger("SELECT EmployeeID FROM Employee WHERE ListID='" + emp.ListID + "' AND QBFile='" + Company + "'"); } new ModelToSQL <Employee>().WriteUpdateToSQL("Employee", emp, "EmployeeID", CommonProcs.WCompanyConnStr); } } UpdateStatusBar("Done", progBar.Maximum); return(employees.Count); }
public int PumpCustomers(string Company) { string sql = "SELECT * FROM Customer WHERE TimeModified > " + CommonProcs.TimeStampString(DateTime.Now.AddDays(-1)); UpdateStatusBar("Opening Quickbooks", 10); List <QBCustomer> customers = new ODBCReaderToModel <QBCustomer>().CreateList(sql, Company); if (customers.Count > 0) { progBar.Maximum = customers.Count; foreach (var QBcust in customers) { UpdateStatusBar("Adding " + QBcust.Name, 1); if (QBcust.Name.Contains("00") || QBcust.Name.Contains("01") || QBcust.Name.Contains("02")) { Customer cust = QBcust.ConvertTo <Customer>(); cust.QBFile = Company; cust.Branch = cust.Name.Substring(0, 2); using (clsDataGetter dg = new clsDataGetter(CommonProcs.WCompanyConnStr)) { cust.CustomerID = dg.GetScalarInteger("SELECT CustomerID FROM Customer WHERE ListID='" + cust.ListID + "' AND QBFile='" + Company + "'"); } new ModelToSQL <Customer>().WriteUpdateToSQL("Customer", cust, "CustomerID", CommonProcs.WCompanyConnStr); } } } UpdateStatusBar("Done", progBar.Maximum); return(customers.Count); }
public int PumpClass(string Company) { string sql = "SELECT * FROM Class WHERE TimeModified > " + CommonProcs.TimeStampString(DateTime.Now.AddDays(-1)); UpdateStatusBar("Opening Quickbooks", 10); List <QBClass> clsItems = new ODBCReaderToModel <QBClass>().CreateList(sql, Company); if (clsItems.Count > 0) { foreach (var QBclass in clsItems) { if (QBclass.Name.Contains("00") || QBclass.Name.Contains("01") || QBclass.Name.Contains("02")) { UpdateStatusBar("Adding " + QBclass.Name, 1); Clss clss = QBclass.ConvertTo <Clss>(); clss.QBFile = Company; clss.Branch = clss.Name.Substring(0, 2); using (clsDataGetter dg = new clsDataGetter(CommonProcs.WCompanyConnStr)) { clss.ClassID = dg.GetScalarInteger("SELECT ClassID FROM Clss WHERE ListID='" + clss.ListID + "' AND QBFile='" + Company + "'"); } new ModelToSQL <Clss>().WriteUpdateToSQL("Clss", clss, "ClassID", CommonProcs.WCompanyConnStr); } } } UpdateStatusBar("Done", progBar.Maximum); return(clsItems.Count); }
public virtual void WriteUpdateToSQL(string tableName, T ModelToSubmit, string pKeyName, string connStr) { var NotMapped = new List <String>(); var props = typeof(T).GetProperties().Where(x => Attribute.IsDefined(x, typeof(NotMappedAttribute))); foreach (var prop in props) { NotMapped.Add(prop.Name); } NotMapped.Add(pKeyName); string sql = "UPDATE " + tableName + " SET "; var properties = typeof(T).GetProperties(); foreach (var prop in properties) { if (!NotMapped.Contains(prop.Name)) { var value = ModelToSubmit.GetType().GetProperty(prop.Name).GetValue(ModelToSubmit); if (value != null) { if (prop.PropertyType.Name == "Int32") { sql += prop.Name + "=" + value + ","; } if (prop.PropertyType.Name == "Decimal") { sql += prop.Name + "=" + value + ","; } else if (prop.PropertyType.Name == "String") { sql += prop.Name + "='" + FSQ(value.ToString()) + "',"; } else if (prop.PropertyType.Name == "Boolean") { sql += prop.Name + "=" + ConvertToBool((bool)value) + ","; } else if (prop.PropertyType.Name == "DateTime") { sql += prop.Name + "='" + value + "',"; } } } } sql = sql.Substring(0, sql.Length - 1); sql += " WHERE " + pKeyName + "=" + ModelToSubmit.GetType().GetProperty(pKeyName).GetValue(ModelToSubmit); using (clsDataGetter dg = new clsDataGetter(connStr)) { dg.RunCommand(sql); } }
public virtual T CreateModel(string sql, string connStr) { var results = new List <T>(); using (clsDataGetter dg = new clsDataGetter(connStr)) { SqlDataReader reader = dg.GetDataReader(sql); var NotMapped = new List <String>(); var props = typeof(T).GetProperties().Where(x => Attribute.IsDefined(x, typeof(NotMappedAttribute))); foreach (var prop in props) { NotMapped.Add(prop.Name); } if (reader == null || reader.HasRows == false) { return(default(T)); } while (reader.Read()) { var item = Activator.CreateInstance <T>(); foreach (var property in typeof(T).GetProperties()) { if (!NotMapped.Contains(property.Name)) { try { if (!reader.IsDBNull(reader.GetOrdinal(property.Name))) { Type convertTo = Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType; property.SetValue(item, Convert.ChangeType(reader[property.Name], convertTo), null); } } catch { } } } results.Add(item); } dg.KillReader(reader); } return(results[0]); }
public virtual int WriteInsertSQL(string tableName, T ModelToSubmit, string pKeyName, string connStr) { int newSubmitID = -1; var NotMapped = new List <String>(); var props = typeof(T).GetProperties().Where(x => Attribute.IsDefined(x, typeof(NotMappedAttribute))); foreach (var prop in props) { NotMapped.Add(prop.Name); } NotMapped.Add(pKeyName); string sql = "INSERT INTO " + tableName + "("; var properties = typeof(T).GetProperties(); foreach (var prop in properties) { if (!NotMapped.Contains(prop.Name)) { sql += prop.Name + ","; } } sql = sql.Substring(0, sql.Length - 1); sql += ") VALUES("; foreach (var prop in properties) { { if (!NotMapped.Contains(prop.Name)) { var propertyType = prop.PropertyType; if (propertyType.IsGenericType && propertyType.GetGenericTypeDefinition() == typeof(Nullable <>)) { propertyType = propertyType.GetGenericArguments()[0]; } var value = ModelToSubmit.GetType().GetProperty(prop.Name).GetValue(ModelToSubmit); if (propertyType.Name == "Int32") { if (value == null) { value = 0; } sql += value + ","; } if (propertyType.Name == "Decimal") { if (value == null) { value = 0.0M; } sql += value + ","; } else if (propertyType.Name == "String") { if (value == null) { value = ""; } sql += "'" + FSQ(value.ToString()) + "',"; } else if (propertyType.Name == "Boolean") { if (value == null) { value = false; } sql += ConvertToBool((bool)value) + ","; } else if (propertyType.Name == "DateTime") { if (value == null) { value = DateTime.Now.ToShortDateString(); } sql += "'" + value + "',"; } } } } sql = sql.Substring(0, sql.Length - 1); sql += ")"; using (clsDataGetter dg = new clsDataGetter(connStr)) { try { dg.RunCommand(sql); } catch (Exception ex) { string x = ex.Message; } newSubmitID = dg.GetScalarInteger("SELECT MAX(" + pKeyName + ") FROM " + tableName); } return(newSubmitID); }