public List<chain_shop_standard_report_item> get_chain_shop_list(string sParentShopId)
{
StringBuilder sb = new StringBuilder();
List<chain_shop_standard_report_item> shopList = new List<chain_shop_standard_report_item>();
string sSql =
"select s.id, s.name " +
"from shop s " +
"where " +
"s.parent_shop=" + sParentShopId + " " +
"order by s.name";
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())
{
chain_shop_standard_report_item shopTotal = new chain_shop_standard_report_item();
shopTotal.iShopId = (int)reader.c("id");
shopTotal.sShopName = (string)reader.c("name");
shopList.Add(shopTotal);
}
} catch (Exception e)
{
shopList = null;
} finally
{
conn.Close();
}
return shopList;
}
public List<chain_shop_standard_report_item> get_chain_shop_total_list(string sParentShopId, DateTime startDate, DateTime endDate)
{
StringBuilder sb = new StringBuilder();
List<chain_shop_standard_report_item> shopList = new List<chain_shop_standard_report_item>();
string sSql =
"select s.id, s.name,count(*) members_accepted_in_period " +
"from consumer c, shop s " +
"where " +
"s.id = c.enrolled_by_shop_id and " +
"c.accepted_membership_at >= '" + buildPostgresDateFromStringDayPrecision(startDate) + "' " +
"and " +
"s.parent_shop=" + sParentShopId + " " +
"and " +
"c.accepted_membership_at <= '" + buildPostgresDateFromStringDayPrecision(endDate) + "' " +
"and " +
"c.pincode_verified='yes' " + // Just a double check
"group by enrolled_by_shop_id, s.name,s.id " +
"order by s.name";
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())
{
chain_shop_standard_report_item shopTotal = new chain_shop_standard_report_item();
shopTotal.iNewMembersInPeriod = Convert.ToInt32(reader.c("members_accepted_in_period").ToString());
shopTotal.iShopId = (int)reader.c("id");
shopTotal.sShopName = (string)reader.c("name");
shopList.Add(shopTotal);
}
} catch (Exception e)
{
shopList = null;
} finally
{
conn.Close();
}
return shopList;
}
