public static int PumpAllPayrollItems(string Company)
{
Console.WriteLine("PayrollItemWage for Company " + Company);
string sql = "SELECT * FROM PayrollItemWage";
List <QBPayrollItem> payItems = new ODBCReaderToModel <QBPayrollItem>().CreateList(sql, Company);
using (clsDataGetter dg = new clsDataGetter(CommonProcs.WCompanyConnStr))
{
dg.RunCommand("DELETE FROM PayrollItem WHERE QBFile='" + Company + "'");
}
if (payItems.Count > 0)
{
foreach (var QBpayItem in payItems)
{
if (QBpayItem.Name.Contains("00") || QBpayItem.Name.Contains("01") || QBpayItem.Name.Contains("02"))
{
PayrollItem payItem = QBpayItem.ConvertTo <PayrollItem>();
Console.WriteLine("PayrollItem " + payItem.Name);
payItem.QBFile = Company;
payItem.Branch = payItem.Name.Substring(0, 2);
new ModelToSQL <PayrollItem>().WriteInsertSQL("PayrollItem", payItem, "payItemID", CommonProcs.WCompanyConnStr);
}
}
}
return(payItems.Count);
}
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();
foreach (var prop in props)
{
NotMapped.Add(prop.Name);
}
while (reader.Read())
{
var item = Activator.CreateInstance <T>();
foreach (var property in typeof(T).GetProperties())
{
if (!NotMapped.Contains(property.Name))
{
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);
}
}
}
results.Add(item);
}
dg.KillReader(reader);
}
return(results[0]);
}
public static int PumpAllClass(string Company)
{
Console.WriteLine("Classes for Company " + Company);
string sql = "SELECT * FROM Class";
List <QBClass> clsItems = new ODBCReaderToModel <QBClass>().CreateList(sql, Company);
using (clsDataGetter dg = new clsDataGetter(CommonProcs.WCompanyConnStr))
{
dg.RunCommand("DELETE FROM Clss WHERE QBFile='" + Company + "'");
}
if (clsItems.Count > 0)
{
foreach (var QBclass in clsItems)
{
if (QBclass.Name.Contains("00") || QBclass.Name.Contains("01") || QBclass.Name.Contains("02"))
{
Clss clss = QBclass.ConvertTo <Clss>();
Console.WriteLine("Class " + clss.Name);
clss.QBFile = Company;
clss.Branch = clss.Name.Substring(0, 2);
new ModelToSQL <Clss>().WriteInsertSQL("Clss", clss, "ClassID", CommonProcs.WCompanyConnStr);
}
}
}
return(clsItems.Count);
}
public static string MapName(string name, string tableName)
{
string sql = "SELECT WPField FROM FieldMap WHERE ";
string returnName = "";
sql += "WPTable='" + tableName + "' ";
sql += "AND QBField = '" + name + "'";
using (clsDataGetter dg = new clsDataGetter(CommonProcs.WCompanyConnStr))
{
returnName = dg.GetScalarString(sql);
if (returnName == string.Empty)
{
returnName = name;
}
}
return(returnName);
}
public static int PumpAllCustomers(string Company)
{
Console.WriteLine("Customers for Company " + Company);
string sql = "SELECT * FROM Customer";
List <QBCustomer> customers = new ODBCReaderToModel <QBCustomer>().CreateList(sql, Company);
using (clsDataGetter dg = new clsDataGetter(CommonProcs.WCompanyConnStr))
{
dg.RunCommand("DELETE FROM Customer WHERE QBFile='" + Company + "'");
}
if (customers.Count > 0)
{
foreach (var QBcust in customers)
{
if (QBcust.Name.ToUpper().Contains("DO NOT USE"))
{
continue;
}
if (QBcust.ParentRefListID != null)
{
Customer cust = QBcust.ConvertTo <Customer>();
Console.WriteLine("Customer " + cust.Name);
cust.QBFile = Company;
cust.Branch = cust.Name.Substring(0, 2);
new ModelToSQL <Customer>().WriteInsertSQL("Customer", cust, "CustomerID", CommonProcs.WCompanyConnStr);
}
else
{
Customer cust = QBcust.ConvertTo <Customer>();
Console.WriteLine("Customer " + cust.Name);
cust.QBFile = Company;
cust.isParent = true;
new ModelToSQL <Customer>().WriteInsertSQL("Customer", cust, "CustomerID", CommonProcs.WCompanyConnStr);
}
}
}
return(customers.Count);
}
public static int PumpAllVendors(string Company)
{
Console.WriteLine("Vendors for Company " + Company);
string sql = "SELECT * FROM Vendor";
List <QBVendor> vendors = new ODBCReaderToModel <QBVendor>().CreateList(sql, Company);
using (clsDataGetter dg = new clsDataGetter(CommonProcs.WCompanyConnStr))
{
dg.RunCommand("DELETE FROM Vendor WHERE QBFile='" + Company + "'");
}
if (vendors.Count > 0)
{
foreach (var QBvend in vendors)
{
Vendor vend = QBvend.ConvertTo <Vendor>(false, "Vendor");
Console.WriteLine("Vendor " + vend.Name);
vend.QBFile = Company;
new ModelToSQL <Vendor>().WriteInsertSQL("Vendor", vend, "VendorID", CommonProcs.WCompanyConnStr, true);
}
}
return(vendors.Count);
}
public static int PumpAllEmployees(string Company)
{
Console.WriteLine("Employees for Company " + Company);
string sql = "SELECT * FROM Employee";
List <QBEmployee> employees = new ODBCReaderToModel <QBEmployee>().CreateList(sql, Company);
using (clsDataGetter dg = new clsDataGetter(CommonProcs.WCompanyConnStr))
{
dg.RunCommand("DELETE FROM Employee WHERE QBFile='" + Company + "'");
}
if (employees.Count > 0)
{
foreach (var QBemp in employees)
{
if (QBemp.PayrollInfoClassRefFullName != null && (QBemp.PayrollInfoClassRefFullName.Contains("00") || QBemp.PayrollInfoClassRefFullName.Contains("01") || QBemp.PayrollInfoClassRefFullName.Contains("02")))
{
Employee emp = QBemp.ConvertTo <Employee>(true, "Employee");
Console.WriteLine("Employee " + emp.LastName + "," + emp.FirstName);
emp.Branch = emp.PayrollInfoClassRefFullName.Substring(0, 2);
emp.QBFile = Company;
new ModelToSQL <Employee>().WriteInsertSQL("Employee", emp, "EmployeeID", CommonProcs.WCompanyConnStr, true);
}
else
{
Employee emp = QBemp.ConvertTo <Employee>(true, "Employee");
Console.WriteLine("Employee " + emp.LastName + "," + emp.FirstName);
emp.Branch = "00";
emp.QBFile = Company;
new ModelToSQL <Employee>().WriteInsertSQL("Employee", emp, "EmployeeID", CommonProcs.WCompanyConnStr);
}
}
}
return(employees.Count);
}
public virtual int WriteInsertSQL(string tableName, T ModelToSubmit, string pKeyName, string connStr, bool hasMapped = false)
{
int newSubmitID = -1;
string mappedName = "";
var NotMapped = new List <String>();
var props = typeof(T).GetProperties();
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];
}
if (hasMapped)
{
mappedName = CommonProcs.MapName(prop.Name, tableName);
}
else
{
mappedName = prop.Name;
}
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);
}
