public GiftCodeItem InsertOneItem(string codeIn, int value, string name, DateTime dateExpired, int isVCash)
{
var lst = new GiftCodeItem();
string sql = "insert into [newDB].[dbo].giftcode(Code, [Values], DateCreated, DateExpired, Gift_ID, IsDelete) OUTPUT INSERTED.id values('" + codeIn + "', @value, GetDate(), @dateExpired, @Gift_ID, 0)";
var dateCreated = DateTime.Now;
var conn = new SqlConnection(Constants.DBConnection);
var MyCommand = new SqlCommand(sql, conn);
conn.Open();
MyCommand.Parameters.Clear();
MyCommand.Parameters.AddWithValue("value", value);
MyCommand.Parameters.AddWithValue("dateExpired", dateExpired.ToString(Constants.DateFormat));
MyCommand.Parameters.AddWithValue("Gift_ID", isVCash);
var inserted = (int)MyCommand.ExecuteScalar();
if (inserted > 0)
{
var item = new GiftCodeItem
{
ID = inserted
};
lst = item;
}
conn.Close();
return(lst);
}
public List <GiftCodeItem> Generator(int total, int value, string name, DateTime dateExpired)
{
var lst = new List <GiftCodeItem>();
string sql = "insert into newDB.giftcode(code, value, dateCreated, dateExpired, used, name) values(@code, @value, @dateCreated, @dateExpired, @used, @name";
DateTime dateCreated = DateTime.Now;
MySqlConnection conn = new MySqlConnection(Constants.DBConnection);
MySqlCommand MyCommand = new MySqlCommand(sql, conn);
conn.Open();
for (int i = 1; i <= total; i++)
{
String code = GenGiftCode();
MyCommand.Parameters.Clear();
MyCommand.Parameters.AddWithValue("code", code);
MyCommand.Parameters.AddWithValue("value", value);
MyCommand.Parameters.AddWithValue("dateCreated", dateCreated.ToString(Constants.DateFormat));
MyCommand.Parameters.AddWithValue("dateExpired", dateExpired.ToString(Constants.DateFormat));
MyCommand.Parameters.AddWithValue("used", 0);
MyCommand.Parameters.AddWithValue("name", name);
int inserted = MyCommand.ExecuteNonQuery();
if (inserted > 0)
{
GiftCodeItem item = new GiftCodeItem();
item.code = code;
item.ID = (int)MyCommand.LastInsertedId;
item.dateCreated = dateCreated;
item.dateExpired = dateExpired;
item.used = false;
item.name = name;
item.value = value;
item.user_id = 0;
item.username = "";
item.fullname = "";
lst.Add(item);
}
}
conn.Close();
return(lst);
}
public List <GiftCodeItem> Generator(string name, DateTime dateExpired, int Gift_ID, string prefix, string note, string giatri, string soluong)
{
List <int> Lstsoluong = soluong.Split(',').Select(Int32.Parse).ToList();
int[] Arrgiatri = giatri.Split(',').Select(n => Convert.ToInt32(n)).ToArray();
int[] Arrsoluong = soluong.Split(',').Select(n => Convert.ToInt32(n)).ToArray();
var notefix = "";
for (int i = 0; i < Arrgiatri.Length; i++)
{
notefix += Arrsoluong[i] + " mã trị giá : " + Arrgiatri[i] + ", ";
}
int total = Lstsoluong.Sum(c => c);
int value = 0;
var turnID = 0;
var conn = new SqlConnection(Constants.DBConnection);
conn.Open();
var lstTurn = new GiftCodeItem();
string sqlTurn = "insert into [newDB].[dbo].[gift_turn](Name, [Quantity],[Prefix],[DateExpired],[IsShow] ,[IsDelete],[TypeID],[Note],DateCreated, NoteFix) OUTPUT INSERTED.ID VALUES(@Name, @Quantity,@Prefix,@DateExpired,1,0,@TypeID,@Note,GETDATE(), @NoteFix)";
var dateCreated = DateTime.Now;
var MyCommand = new SqlCommand(sqlTurn, conn);
MyCommand.Parameters.Clear();
MyCommand.Parameters.AddWithValue("Quantity", total);
MyCommand.Parameters.AddWithValue("Prefix", prefix);
MyCommand.Parameters.AddWithValue("DateExpired", dateExpired.ToString(Constants.DateFormat));
MyCommand.Parameters.AddWithValue("TypeID", Gift_ID);
MyCommand.Parameters.AddWithValue("Note", note);
MyCommand.Parameters.AddWithValue("Name", name);
MyCommand.Parameters.AddWithValue("NoteFix", notefix);
turnID = (int)MyCommand.ExecuteScalar();
var lst = new List <GiftCodeItem>();
for (int j = 0; j < Arrsoluong.Length; j++)
{
for (int i = 1; i <= Arrsoluong[j]; i++)
{
string sql = "insert into [newDB].[dbo].giftcode(Code, DateCreated,DateExpired, TurnID, Gift_ID, IsDelete, Money) OUTPUT INSERTED.id values('" + prefix.ToUpper() + RandomString(10) + "', GetDate(), @dateExpired, @turnID, @Gift_ID, 0, @Money)";
var myCommand = new SqlCommand(sql, conn);
myCommand.Parameters.Clear();
myCommand.Parameters.AddWithValue("value", value);
myCommand.Parameters.AddWithValue("dateExpired", dateExpired.ToString(Constants.DateFormat));
myCommand.Parameters.AddWithValue("Gift_ID", Gift_ID);
myCommand.Parameters.AddWithValue("turnID", turnID);
myCommand.Parameters.AddWithValue("Money", Convert.ToDecimal(Arrgiatri[j]));
var inserted = (int)myCommand.ExecuteScalar();
if (inserted > 0)
{
var item = new GiftCodeItem
{
ID = inserted
};
lst.Add(item);
}
}
}
conn.Close();
return(lst);
}
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);
}
