public List<dim_value> dash_maxi_month_member_old(int iYear, string sChainId)
{
List<dim_value> monthList = new List<dim_value>();
// Mountain dashboard 008
string sSql =
"select EXTRACT(MONTH FROM accepted_membership_at) accepted_month, count(*) month_value " +
"from consumer c, shop s " +
"where " +
"s.parent_shop=" + sChainId + " and " +
"s.id = c.enrolled_by_shop_id and " +
"pincode_verified='yes' " +
"and " +
"EXTRACT(YEAR FROM accepted_membership_at)=" + iYear.ToString() + " " +
"group by EXTRACT(MONTH FROM accepted_membership_at) " +
"order by EXTRACT(MONTH FROM accepted_membership_at) ";
GLOBAL_SQL_CONN conn = new GLOBAL_SQL_CONN(this);
try
{
GLOBAL_SQL_COMMAND command = new GLOBAL_SQL_COMMAND(sSql, conn);
GLOBAL_SQL_READER reader = new GLOBAL_SQL_READER(command);
while (reader.Read())
{
dim_value month = new dim_value();
month.sDim = reader.c("accepted_month").ToString();
month.sValue = reader.c("month_value").ToString();
monthList.Add(month);
}
} catch (Exception e)
{
monthList = null;
} finally
{
conn.Close();
}
return monthList;
}
public List<dim_value> get_monthly_invoice(string sChainId)
{
string sSql =
"select " +
"s.name, sum(cb.basket_total_sum) shop_sum " +
"from consumer_basket cb, consumer_paytool cp, consumer c, shop s " +
"where " +
"cb.shop_id = s.id " +
"and " +
"cb.consumer_paytool_id = cp.id " +
"and " +
"cp.consumer_id = c.id " +
"and " +
"cb.confirmed_by_shop='yes' " +
"group by s.name " +
"order by s.name";
/* Med 60 dagers liggetid ... */
/*
string sSql =
"select " +
"s.name, sum(cb.basket_total_sum) shop_sum " +
"from consumer_basket cb, consumer_paytool cp, consumer c, shop s " +
"where " +
"cb.shop_id = s.id " +
"and " +
"cb.consumer_paytool_id = cp.id " +
"and " +
"cp.consumer_id = c.id " +
"and " +
"c.pincode_verified='yes' " +
"and " +
"cb.confirmed_by_shop='yes' " +
"and " +
"(EXTRACT(DOY FROM timestamp) + EXTRACT(YEAR FROM timestamp) * 365) > (EXTRACT(DOY FROM accepted_membership_at) + EXTRACT(YEAR FROM accepted_membership_at) * 365) -60 " +
"group by s.name " +
"order by s.name";
*/
GLOBAL_SQL_CONN conn = new GLOBAL_SQL_CONN(this);
List<dim_value> shopList = new List<dim_value>();
try
{
GLOBAL_SQL_COMMAND command = new GLOBAL_SQL_COMMAND(sSql, conn);
GLOBAL_SQL_READER reader = new GLOBAL_SQL_READER(command);
while (reader.Read())
{
dim_value dimValue = new dim_value();
dimValue.sDim = reader.c("name").ToString();
dimValue.sValue = reader.c("shop_sum").ToString();
shopList.Add(dimValue);
}
} catch (Exception e)
{
return null;
} finally
{
conn.Close();
}
return shopList;
}
public List<dim_value> dash_mini_month_member_old(string sYear, string sChainId)
{
List<dim_value> yearList = new List<dim_value>();
string sSql =
"select count(*) year_value from " +
"consumer c, shop s " +
"where pincode_verified='yes' and " +
" c.enrolled_by_shop_id=s.id and s.parent_shop=" + sChainId + " " +
"EXTRACT(YEAR FROM accepted_membership_at)=" + sYear;
GLOBAL_SQL_CONN conn = new GLOBAL_SQL_CONN(this);
try
{
GLOBAL_SQL_COMMAND command = new GLOBAL_SQL_COMMAND(sSql, conn);
GLOBAL_SQL_READER reader = new GLOBAL_SQL_READER(command);
while (reader.Read())
{
dim_value month = new dim_value();
month.sDim = sYear;
month.sValue = reader.c("year_value").ToString();
yearList.Add(month);
}
} catch (Exception e)
{
yearList = null;
} finally
{
conn.Close();
}
return yearList;
}
public List<dim_value> get_consumer_sex_list(string sChainId, DASHBOARD_PERIOD currentPeriod)
{
List<dim_value> sexList = new List<dim_value>();
DASHBOARD_MONTH yearMonth = new DASHBOARD_MONTH(currentPeriod);
string sPeriodFilter = " ";
if (currentPeriod != DASHBOARD_PERIOD.DONT_CARE)
{
string sFromMonthDate = yearMonth.getThisDbYearMonthDay1String(0);
string sBeforeMonthDate = yearMonth.getThisDbYearNextMonthDay1String();
sPeriodFilter =
" and " + // lunch
"c.accepted_membership_at >= '" + sFromMonthDate + "' " +
"and " +
"c.accepted_membership_at < '" + sBeforeMonthDate + "' ";
}
// Mountain dashboard 006 Antall medlemmer etter kjønn
string sSql =
"select c.sex, count(*) nof_sex " +
"from consumer c, shop s " +
"where c.enrolled_by_shop_id=s.id and s.parent_shop=" + sChainId + " " +
sPeriodFilter +
"and c.pincode_verified='yes' " +
"and " +
"(sex = 'female' or sex = 'male') " +
"group by c.sex ";
GLOBAL_SQL_CONN conn = new GLOBAL_SQL_CONN(this);
try
{
GLOBAL_SQL_COMMAND command = new GLOBAL_SQL_COMMAND(sSql, conn);
GLOBAL_SQL_READER reader = new GLOBAL_SQL_READER(command);
while (reader.Read())
{
dim_value sex = new dim_value();
sex.sDim = reader.c("sex").ToString();
sex.sValue = reader.c("nof_sex").ToString();
sexList.Add(sex);
}
} catch (Exception e)
{
sexList = null;
} finally
{
conn.Close();
}
return sexList;
}
