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 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 <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);
}