public DateRange GetReportDateRange()
{
DateTime w_today = DateTime.Today;
int w_min_year = w_today.Year;
int w_min_month = w_today.Month;
int w_max_year = w_today.Year;
int w_max_month = w_today.Month;
string w_query = "SELECT MIN(year || '-' || month) AS min, MAX(year || '-' || month) AS max FROM tbl_report";
SQLiteDataReader w_reader = DbAssist.executeQuery(w_query);
if (w_reader != null)
{
w_reader.Read();
string w_min = w_reader.GetString(0);
string w_max = w_reader.GetString(1);
string[] w_mins = w_min.Split('-');
w_min_year = int.Parse(w_mins[0]);
w_min_month = int.Parse(w_mins[1]);
string[] w_maxs = w_max.Split('-');
w_max_year = int.Parse(w_maxs[0]);
w_max_month = int.Parse(w_maxs[1]);
}
DateRange w_range = new DateRange();
w_range.m_begin_year = w_min_year;
w_range.m_begin_month = w_min_month;
w_range.m_end_year = w_max_year;
w_range.m_end_month = w_max_month;
return(w_range);
}
public IList <ITeam> GetItemList()
{
IList <ITeam> w_list = new List <ITeam>();
string w_query = string.Format(@"SELECT * FROM {0};"
, m_tableName
);
SQLiteDataReader w_reader = DbAssist.executeQuery(w_query);
if (w_reader != null)
{
while (w_reader.Read())
{
Team w_item = new Team();
//. 01 - id
w_item.id = w_reader.GetInt32(0);
//. 02 - name
w_item.name = w_reader.GetString(1);
//. 03 - desc
w_item.desc = w_reader.GetString(2);
w_list.Add(w_item);
}
}
return(w_list);
}
protected IList <IReport> GetItemList(int year, List <int> months)
{
string w_query = "";
IList <IReport> w_list = new List <IReport>();
if (months.Count == 1)
{
w_query = string.Format(@"SELECT * FROM {0} WHERE year='{1}' AND month='{2}';"
, m_tableName
, year
, months[0]
);
}
else if (months.Count > 1)
{
string w_monthList = "";
foreach (int month in months)
{
w_monthList += string.Format("'{0}', ", month);
}
w_monthList = w_monthList.Substring(0, w_monthList.Length - 2);
w_query = string.Format(@"SELECT id, year, month, day, employee, product, sum(amount) as amount
FROM {0}
WHERE year = '{1}' AND month IN ({2})
GROUP BY employee, product
ORDER BY employee, product;"
, m_tableName
, year
, w_monthList
);
}
SQLiteDataReader w_reader = DbAssist.executeQuery(w_query);
if (w_reader != null)
{
while (w_reader.Read())
{
Report w_item = new Report();
//. 01 - id
w_item.id = w_reader.GetInt32(0);
//. 02 - year
w_item.year = w_reader.GetInt32(1);
//. 03 - month
w_item.month = w_reader.GetInt32(2);
//. 04 - day
w_item.day = w_reader.GetInt32(3);
//. 05 - employee
w_item.employee = w_reader.GetInt32(4);
//. 06 - product
w_item.product = w_reader.GetInt32(5);
//. 07 - amount
w_item.amount = w_reader.GetInt32(6);
w_list.Add(w_item);
}
}
return(w_list);
}
public bool DeleteAll()
{
string w_query = string.Format(@"DELETE FROM {0};"
, m_tableName
);
bool w_ret = DbAssist.executeCommand(w_query);
return(w_ret);
}
public bool UpdateItem(ITeam udTeam)
{
bool w_ret = false;
string w_query = string.Format(@"UPDATE {0} SET name='{1}', desc='{2}';"
, m_tableName
, udTeam.name
, udTeam.desc
);
w_ret = DbAssist.executeCommand(w_query);
return(w_ret);
}
public bool AddItem(ITeam newTeam)
{
bool w_ret = false;
string w_query = string.Format(@"INSERT INTO {0} (name, desc) VALUES ('{1}', '{2}')"
, m_tableName
, newTeam.name
, newTeam.desc
);
w_ret = DbAssist.executeCommand(w_query);
return(w_ret);
}
public bool DeleteItem(ITeam delTeam)
{
bool w_ret = false;
string w_query = string.Format(@"DELETE FROM {0} WHERE id='{1}';"
, m_tableName
, delTeam.id
);
w_ret = DbAssist.executeCommand(w_query);
return(w_ret);
}
public bool DeleteAll(int year, int month)
{
bool w_ret = false;
string w_query = string.Format(@"DELETE FROM {0} WHERE year='{1}' AND month='{2}';"
, m_tableName
, year
, month
);
w_ret = DbAssist.executeCommand(w_query);
return(w_ret);
}
public bool AddItem(IEmployee newEmployee)
{
bool w_ret = false;
string w_query = string.Format(@"INSERT INTO {0} (f_name, g_name, email, code, team, invisible) VALUES ('{1}', '{2}', '{3}', '{4}', '{5}', '{6}')"
, m_tableName
, newEmployee.f_name
, newEmployee.g_name
, newEmployee.email
, newEmployee.code == "full time" ? 1 : 0
, newEmployee.team
, newEmployee.invisible
);
w_ret = DbAssist.executeCommand(w_query);
return(w_ret);
}
public bool AddItem(IReport newItem)
{
bool w_ret = false;
string w_query = string.Format(@"INSERT INTO {0} (year, month, day, employee, product, amount) VALUES ('{1}', '{2}', '{3}', '{4}', '{5}', '{6}')"
, m_tableName
, newItem.year
, newItem.month
, newItem.day
, newItem.employee
, newItem.product
, newItem.amount
);
w_ret = DbAssist.executeCommand(w_query);
return(w_ret);
}
public bool UpdateItem(IEmployee udEmployee)
{
bool w_ret = false;
string w_query = string.Format(@"UPDATE {0} SET f_name='{1}', g_name='{2}', email='{3}', code='{4}', team='{5}', invisible='{6}' WHERE id='{7}';"
, m_tableName
, udEmployee.f_name
, udEmployee.g_name
, udEmployee.email
, udEmployee.code == "full time" ? 1 : 0
, udEmployee.team
, udEmployee.invisible
, udEmployee.id
);
w_ret = DbAssist.executeCommand(w_query);
return(w_ret);
}
public bool UpdateItem(IReport udReport)
{
bool w_ret = false;
string w_query = string.Format(@"UPDATE {0} SET year='{1}', month='{2}', day='{3}', employee='{4}', product='{5}', amount='{6}' WHERE id='{7}';"
, m_tableName
, udReport.year
, udReport.month
, udReport.day
, udReport.employee
, udReport.product
, udReport.amount
, udReport.id
);
w_ret = DbAssist.executeCommand(w_query);
return(w_ret);
}
public bool AddItem(IProduct newItem)
{
bool w_ret = false;
string w_query = string.Format(@"INSERT INTO {0} (id, name, type, cost, price, weight, invisible) VALUES ('{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}')"
, m_tableName
, newItem.id
, newItem.name
, newItem.type == "Group" ? 1 : 0
, newItem.cost
, newItem.price
, newItem.weight
, newItem.invisible
);
w_ret = DbAssist.executeCommand(w_query);
return(w_ret);
}
public bool UpdateItem(IProduct udProduct)
{
bool w_ret = false;
string w_query = string.Format(@"UPDATE {0} SET name='{1}', type='{2}', cost='{3}', price='{4}', weight='{5}', invisible='{6}' WHERE id='{7}';"
, m_tableName
, udProduct.name
, udProduct.type == "Group" ? 1 : 0
, udProduct.cost
, udProduct.price
, udProduct.weight
, udProduct.invisible
, udProduct.id
);
w_ret = DbAssist.executeCommand(w_query);
return(w_ret);
}
public IList <IEmployee> GetItemList()
{
IList <IEmployee> w_list = new List <IEmployee>();
string w_query = string.Format(@"SELECT * FROM {0};"
, m_tableName
);
SQLiteDataReader w_reader = DbAssist.executeQuery(w_query);
if (w_reader != null)
{
while (w_reader.Read())
{
Employee w_item = new Employee();
//. 01 - id
w_item.id = w_reader.GetInt32(0);
//. 02 - f_name
w_item.f_name = w_reader.GetString(1);
//. 03 - g_name
w_item.g_name = w_reader.GetString(2);
//. 04 - email
w_item.email = w_reader.GetString(3);
//. 05 - code
w_item.code = w_reader.GetInt32(4) == 1 ? "full time" : "part time";
//. 06 - team
w_item.team = w_reader.GetInt32(5);
//. 07 - invisible
w_item.invisible = w_reader.GetInt32(6); //x w_reader.GetBoolean(6);
w_list.Add(w_item);
}
}
return(w_list);
}
public IList <IProduct> GetItemList()
{
IList <IProduct> w_list = new List <IProduct>();
string w_query = string.Format(@"SELECT * FROM {0};"
, m_tableName
);
SQLiteDataReader w_reader = DbAssist.executeQuery(w_query);
if (w_reader != null)
{
while (w_reader.Read())
{
Product w_item = new Product();
//. 01 - id
w_item.id = w_reader.GetInt32(0);
//. 02 - name
w_item.name = w_reader.GetString(1);
//. 03 - type
w_item.type = w_reader.GetInt32(2) == 1 ? "Group" : "Individual";
//. 04 - cost
w_item.cost = w_reader.GetFloat(3);
//. 05 - price
w_item.price = w_reader.GetFloat(4);
//. 06 - weight
w_item.weight = w_reader.GetFloat(5);
//. 07 - disable
w_item.invisible = w_reader.GetInt32(6);
w_list.Add(w_item);
}
}
return(w_list);
}
