public GiftCodeResult GetAll(bool used, int skip, int take)
{
var rs = new GiftCodeResult();
List <GiftCodeItem> data = new List <GiftCodeItem>();
int totalRecord = 0;
string sql = "";
if (used)
{
if (used == true)
{
sql = "SELECT u.username, gc.* FROM [newDB].[dbo].giftcode gc inner join [newDB].[dbo].g_user u on gc.user_id = u.user_id WHERE gc.user_id > 0 ORDER BY gc.id DESC OFFSET @skip ROWS FETCH NEXT @take ROWS ONLY";
}
else
{
sql = "SELECT u.username, gc.* FROM [newDB].[dbo].giftcode gc inner join [newDB].[dbo].g_user u on gc.user_id = u.user_id ORDER BY gc.id DESC OFFSET @skip ROWS FETCH NEXT @take ROWS ONLY";
}
SqlConnection conn = new SqlConnection(Constants.DBConnection);
SqlCommand MyCommand = new SqlCommand(sql, conn);
MyCommand.Parameters.AddWithValue("skip", skip);
MyCommand.Parameters.AddWithValue("take", take);
SqlDataReader MyReader;
conn.Open();
MyReader = MyCommand.ExecuteReader();
while (MyReader.Read())
{
var item = new GiftCodeItem
{
code = MyReader.GetString(MyReader.GetOrdinal("code")),
ID = MyReader.GetInt32(MyReader.GetOrdinal("id")),
dateCreated = MyReader.GetDateTime(MyReader.GetOrdinal("dateCreated")),
dateExpired = MyReader.GetDateTime(MyReader.GetOrdinal("dateExpired")),
name = MyReader.GetString(MyReader.GetOrdinal("name")),
value = MyReader.GetInt32(MyReader.GetOrdinal("value")),
user_id = MyReader.GetInt32(MyReader.GetOrdinal("user_id")),
username = MyReader.GetString(MyReader.GetOrdinal("username")),
fullname = MyReader.GetString(MyReader.GetOrdinal("fullname")),
isVCash = MyReader.GetInt32(MyReader.GetOrdinal("isVCash"))
};
item.sdateCreated = item.dateCreated.ToShortDateString();
item.sdateExpired = item.dateExpired.ToShortDateString();
data.Add(item);
}
MyReader.Close();
sql = "Select count(*) as totalRecord from [portal].[dbo].giftcode WHERE used = 1";
MyCommand = new SqlCommand(sql, conn);
MyReader = MyCommand.ExecuteReader();
while (MyReader.Read())
{
totalRecord = MyReader.GetInt32(MyReader.GetOrdinal("totalRecord"));
}
MyReader.Close();
conn.Close();
}
else
{
sql = "SELECT * FROM [portal].[dbo].giftcode WHERE used = 0 ORDER BY dateCreated DESC OFFSET @skip ROWS FETCH NEXT @take ROWS ONLY";
SqlConnection conn = new SqlConnection(Constants.DBConnection);
SqlCommand MyCommand = new SqlCommand(sql, conn);
MyCommand.Parameters.AddWithValue("skip", skip);
MyCommand.Parameters.AddWithValue("take", take);
SqlDataReader MyReader;
conn.Open();
MyReader = MyCommand.ExecuteReader();
while (MyReader.Read())
{
GiftCodeItem item = new GiftCodeItem();
item.code = MyReader.GetString(MyReader.GetOrdinal("code"));
item.ID = MyReader.GetInt32(MyReader.GetOrdinal("id"));
item.dateCreated = MyReader.GetDateTime(MyReader.GetOrdinal("dateCreated"));
item.dateExpired = MyReader.GetDateTime(MyReader.GetOrdinal("dateExpired"));;
item.used = false;
item.name = MyReader.GetString(MyReader.GetOrdinal("name"));
item.value = MyReader.GetInt32(MyReader.GetOrdinal("value"));
item.isVCash = MyReader.GetInt32(MyReader.GetOrdinal("isVCash"));
item.user_id = 0;
item.username = "";
item.fullname = "";
item.sdateCreated = item.dateCreated.ToShortDateString();
item.sdateExpired = item.dateExpired.ToShortDateString();
data.Add(item);
}
MyReader.Close();
sql = "Select count(*) as totalRecord from [portal].[dbo].giftcode WHERE used = 0";
MyCommand = new SqlCommand(sql, conn);
MyReader = MyCommand.ExecuteReader();
while (MyReader.Read())
{
totalRecord = MyReader.GetInt32(MyReader.GetOrdinal("totalRecord"));
}
MyReader.Close();
conn.Close();
}
rs.totalRecord = totalRecord;
rs.data = data;
return(rs);
}
public GiftCodeResult GetAll(bool used, int skip, int take)
{
var rs = new GiftCodeResult();
List <GiftCodeItem> data = new List <GiftCodeItem>();
int totalRecord = 0;
string sql = "";
if (used)
{
sql = "SELECT u.username, u.fullname, gc.* FROM newDB.giftcode gc inner join newDB.view_user_info u on gc.user_id = u.id WHERE used = 1 ORDER BY gc.id DESC LIMIT @skip, @take";
MySqlConnection conn = new MySqlConnection(Constants.DBConnection);
MySqlCommand MyCommand = new MySqlCommand(sql, conn);
MyCommand.Parameters.AddWithValue("skip", skip);
MyCommand.Parameters.AddWithValue("take", take);
MySqlDataReader MyReader;
conn.Open();
MyReader = MyCommand.ExecuteReader();
while (MyReader.Read())
{
GiftCodeItem item = new GiftCodeItem();
item.code = MyReader.GetString("code");
item.ID = MyReader.GetInt32("id");
item.dateCreated = MyReader.GetDateTime("dateCreated");
item.dateExpired = MyReader.GetDateTime("dateExpired");;
item.used = false;
item.name = MyReader.GetString("name");
item.value = MyReader.GetInt32("value");
item.user_id = MyReader.GetInt32("user_id");
item.username = MyReader.GetString("username");
item.fullname = MyReader.GetString("fullname");
data.Add(item);
}
MyReader.Close();
sql = "Select count(*) as totalRecord from newDB.giftcode WHERE used = 1";
MyCommand = new MySqlCommand(sql, conn);
MyReader = MyCommand.ExecuteReader();
while (MyReader.Read())
{
totalRecord = MyReader.GetInt32("totalRecord");
}
MyReader.Close();
conn.Close();
}
else
{
sql = "SELECT * FROM newDB.giftcode WHERE used = 0 ORDER BY id DESC LIMIT @skip, @take";
MySqlConnection conn = new MySqlConnection(Constants.DBConnection);
MySqlCommand MyCommand = new MySqlCommand(sql, conn);
MyCommand.Parameters.AddWithValue("skip", skip);
MyCommand.Parameters.AddWithValue("take", take);
MySqlDataReader MyReader;
conn.Open();
MyReader = MyCommand.ExecuteReader();
while (MyReader.Read())
{
GiftCodeItem item = new GiftCodeItem();
item.code = MyReader.GetString("code");
item.ID = MyReader.GetInt32("id");
item.dateCreated = MyReader.GetDateTime("dateCreated");
item.dateExpired = MyReader.GetDateTime("dateExpired");;
item.used = false;
item.name = MyReader.GetString("name");
item.value = MyReader.GetInt32("value");
item.user_id = 0;
item.username = "";
item.fullname = "";
data.Add(item);
}
MyReader.Close();
sql = "Select count(*) as totalRecord from newDB.giftcode WHERE used = 0";
MyCommand = new MySqlCommand(sql, conn);
MyReader = MyCommand.ExecuteReader();
while (MyReader.Read())
{
totalRecord = MyReader.GetInt32("totalRecord");
}
MyReader.Close();
conn.Close();
}
rs.totalRecord = totalRecord;
rs.data = data;
return(rs);
}