/*update 24/03*/
public List <GameTextItem> GetAll()
{
var lst = new List <GameTextItem>();
string sql = "SELECT * FROM [portal].[dbo].chuchaychay ORDER BY id DESC";
SqlConnection conn = new SqlConnection(Constants.DBConnection);
SqlCommand MyCommand = new SqlCommand(sql, conn);
SqlDataReader MyReader;
conn.Open();
MyReader = MyCommand.ExecuteReader();
while (MyReader.Read())
{
var item = new GameTextItem
{
ID = MyReader.GetInt32(MyReader.GetOrdinal("id")),
Title = MyReader.GetString(MyReader.GetOrdinal("Title")),
Link = MyReader.GetString(MyReader.GetOrdinal("Link")),
DataStart = MyReader.GetDateTime(MyReader.GetOrdinal("DataStart")),
DateEnd = MyReader.GetDateTime(MyReader.GetOrdinal("DateEnd")),
IsDelete = MyReader.GetBoolean(MyReader.GetOrdinal("IsDelete")),
Order = MyReader.GetInt32(MyReader.GetOrdinal("Orders")),
DataStartstring = MyReader.GetDateTime(MyReader.GetOrdinal("DataStart")).ToShortDateString(),
DateEndstring = MyReader.GetDateTime(MyReader.GetOrdinal("DateEnd")).ToShortDateString()
};
lst.Add(item);
}
MyReader.Close();
conn.Close();
return(lst);
}
public GameTextItem GetOne(int ID)
{
var item = new GameTextItem();
string sql = "SELECT * FROM [portal].[dbo].chuchaychay WHERE id = @id";
SqlConnection conn = new SqlConnection(Constants.DBConnection);
SqlCommand MyCommand = new SqlCommand(sql, conn);
MyCommand.Parameters.AddWithValue("id", ID);
SqlDataReader MyReader;
conn.Open();
MyReader = MyCommand.ExecuteReader();
while (MyReader.Read())
{
item = new GameTextItem
{
ID = MyReader.GetInt32(MyReader.GetOrdinal("id")),
Title = MyReader.GetString(MyReader.GetOrdinal("Title")),
Link = MyReader.GetString(MyReader.GetOrdinal("Link")),
DataStart = MyReader.GetDateTime(MyReader.GetOrdinal("DataStart")),
DateEnd = MyReader.GetDateTime(MyReader.GetOrdinal("DateEnd")),
IsDelete = MyReader.GetBoolean(MyReader.GetOrdinal("IsDelete")),
Order = MyReader.GetInt32(MyReader.GetOrdinal("Orders")),
};
}
MyReader.Close();
conn.Close();
return(item);
}
public List <AnnouncementItem> GetAll(int currentRecord = 0, int numberRecord = 10)
{
var lst = new List <AnnouncementItem>();
string sql = "SELECT * FROM [newDB].[dbo].[event] where [IsDelete] = 0 order by id desc OFFSET " + currentRecord + " ROWS FETCH NEXT " + numberRecord + "ROWS ONLY ";
SqlConnection conn = new SqlConnection(Constants.DBConnection);
SqlCommand MyCommand = new SqlCommand(sql, conn);
SqlDataReader MyReader;
conn.Open();
MyReader = MyCommand.ExecuteReader();
while (MyReader.Read())
{
var item = new AnnouncementItem
{
ID = MyReader.GetInt32(MyReader.GetOrdinal("id")),
DisplayOrder = MyReader.GetInt32(MyReader.GetOrdinal("DisplayOrder")),
begin_time = MyReader.GetDateTime(MyReader.GetOrdinal("DateStart")),
end_time = MyReader.GetDateTime(MyReader.GetOrdinal("DateEnd")),
Subject = MyReader.GetString(MyReader.GetOrdinal("name")),
Content = MyReader.GetString(MyReader.GetOrdinal("content")),
};
lst.Add(item);
}
MyReader.Close();
conn.Close();
return(lst);
}
public AnnouncementItem GetOne(int ID)
{
var item = new AnnouncementItem();
string sql = "SELECT * FROM [newDB].[dbo].[event] WHERE id = @id";
SqlConnection conn = new SqlConnection(Constants.DBConnection);
SqlCommand MyCommand = new SqlCommand(sql, conn);
MyCommand.Parameters.AddWithValue("id", ID);
SqlDataReader MyReader;
conn.Open();
MyReader = MyCommand.ExecuteReader();
while (MyReader.Read())
{
item.ID = MyReader.GetInt32(MyReader.GetOrdinal("id"));
item.begin_time = MyReader.GetDateTime(MyReader.GetOrdinal("DateStart"));
item.end_time = MyReader.GetDateTime(MyReader.GetOrdinal("DateEnd"));
item.Subject = MyReader.GetString(MyReader.GetOrdinal("name"));
item.UrlImage = MyReader.GetString(MyReader.GetOrdinal("UrlImage"));
item.Content = MyReader.GetString(MyReader.GetOrdinal("content"));
item.DoiTuong = MyReader.GetString(MyReader.GetOrdinal("DoiTuong"));
item.ThoiGian = MyReader.GetString(MyReader.GetOrdinal("ThoiGian"));
item.GameID = MyReader.GetInt32(MyReader.GetOrdinal("GameID"));
item.DisplayOrder = MyReader.GetInt32(MyReader.GetOrdinal("DisplayOrder"));
item.begin_timestring = item.begin_time.ToString(Constants.DateFormat);
item.end_timestring = item.end_time.ToString(Constants.DateFormat);
}
MyReader.Close();
conn.Close();
return(item);
}
private void comboBox_misCultivos_SelectedIndexChanged(object sender, EventArgs e)
{
Random random = new Random();
int num = random.Next(100);
textBox1.Text = num.ToString() + " %";
DateTime today = DateTime.Today;
double diff = 0;
double porcentaje = 0;
MySqlConnection conexion = Conectar();
String sentencia = "SELECT fecha_siembra FROM mis_cultivos WHERE nombre = '" + comboBox_misCultivos.Text + "'";
MySqlCommand comando = new MySqlCommand(sentencia, conexion);
conexion.Open();
comando.ExecuteNonQuery();
MySqlDataReader MyReader;
MyReader = comando.ExecuteReader();
while (MyReader.Read())
{
var dateString = MyReader.GetDateTime(MyReader.GetOrdinal("fecha_siembra")).ToString("yyyy/MM/dd");
DateTime myDate = DateTime.Parse(dateString);
diff = (today - myDate).TotalDays;
}
MyReader.Close();
String sentencia2 = "UPDATE mis_cultivos SET dias_transcurridos = " + diff + " WHERE nombre = '" + comboBox_misCultivos.Text + "'";
MySqlCommand comando2 = new MySqlCommand(sentencia2, conexion);
comando2.ExecuteNonQuery();
String sentencia3 = "SELECT dias_desarrollo FROM mis_cultivos WHERE nombre = '" + comboBox_misCultivos.Text + "'";
MySqlCommand comando3 = new MySqlCommand(sentencia3, conexion);
comando3.ExecuteNonQuery();
MyReader = comando3.ExecuteReader();
while (MyReader.Read())
{
double dias = MyReader.GetDouble("dias_desarrollo");
porcentaje = (diff * 100) / dias;
}
porcentaje = Math.Round(porcentaje, 2);
MyReader.Close();
conexion.Close();
descripcion.Text = "Su cultivo tiene " + diff.ToString() + " días de haberse sembrado, lo que corresponde a " + porcentaje + "% de su estación de crecimiento. Recuerda que en esta etapa es importante...(recomendación) " + "y debería parecerse a la siguiente imagen: ";
}
/// <summary>
/// Danh sách lịch sử các trận đã chơi theo game Id
/// </summary>
/// <param name="zoneId"></param>
/// <param name="skip"></param>
/// <param name="take"></param>
/// <returns></returns>
public GameHistoryResult GetGameHistoryByZoneID(int zoneId, int skip, int take)
{
var rs = new GameHistoryResult();
var lst = new List <GameHistoryItem>();
var totalRecord = 0;
var sql = "SELECT a.*, b.username, b.fullname FROM [portal].[dbo].game_history a inner join [portal].[dbo].view_user_info b on a.user_id = b.id WHERE game_id = @zoneId ORDER BY id DESC OFFSET @skip ROWS FETCH NEXT @take ROWS ONLY";
var sqlTotalQuery = "SELECT count(*) as totalRecord FROM [portal].[dbo].game_history WHERE game_id = @zoneId";
var conn = new SqlConnection(Constants.DBConnection);
var MyCommand = new SqlCommand(sql, conn);
MyCommand.Parameters.AddWithValue("zoneId", zoneId);
MyCommand.Parameters.AddWithValue("skip", skip);
MyCommand.Parameters.AddWithValue("take", take);
SqlDataReader MyReader;
conn.Open();
MyReader = MyCommand.ExecuteReader();
while (MyReader.Read())
{
var item1 = new GameHistoryItem
{
id = MyReader.GetInt32(MyReader.GetOrdinal("id")),
user_id = MyReader.GetInt32(MyReader.GetOrdinal("user_id")),
cash = MyReader.GetInt64(MyReader.GetOrdinal("cash")),
current_cash = MyReader.GetInt64(MyReader.GetOrdinal("current_cash")),
description = MyReader.GetString(MyReader.GetOrdinal("description")),
game_id = MyReader.GetInt32(MyReader.GetOrdinal("game_id")),
trans_type = MyReader.GetInt32(MyReader.GetOrdinal("trans_type")),
time = MyReader.GetDateTime(MyReader.GetOrdinal("time")),
before_cash = MyReader.GetInt64(MyReader.GetOrdinal("before_cash")),
username = MyReader.GetString(MyReader.GetOrdinal("username")),
fullname = MyReader.GetString(MyReader.GetOrdinal("fullname"))
};
lst.Add(item1);
}
//close reader cũ
MyReader.Close();
MyCommand = new SqlCommand(sqlTotalQuery, conn);
MyCommand.Parameters.AddWithValue("zoneId", zoneId);
MyReader = MyCommand.ExecuteReader();
while (MyReader.Read())
{
totalRecord = MyReader.GetInt32(MyReader.GetOrdinal("totalRecord"));
}
conn.Close();
rs.data = lst;
rs.totalRecord = totalRecord;
return(rs);
}
/// <summary>
/// Danh sách lịch sử các trận đã chơi theo game Id
/// </summary>
/// <param name="zoneId"></param>
/// <param name="skip"></param>
/// <param name="take"></param>
/// <returns></returns>
public GameHistoryResult GetGameHistoryByZoneID(int zoneId, int skip, int take)
{
var rs = new GameHistoryResult();
var lst = new List <GameHistoryItem>();
int totalRecord = 0;
string sql = "SELECT a.*, b.username, b.fullname FROM newDB.game_history a inner join newDB.view_user_info b on a.user_id = b.id WHERE game_id = @zoneId ORDER BY id DESC LIMIT @skip, @take";
string sqlTotalQuery = "SELECT count(*) as totalRecord FROM newDB.game_history WHERE game_id = @zoneId";
MySqlConnection conn = new MySqlConnection(Constants.DBConnection);
MySqlCommand MyCommand = new MySqlCommand(sql, conn);
MyCommand.Parameters.AddWithValue("zoneId", zoneId);
MyCommand.Parameters.AddWithValue("skip", skip);
MyCommand.Parameters.AddWithValue("take", take);
MySqlDataReader MyReader;
conn.Open();
MyReader = MyCommand.ExecuteReader();
while (MyReader.Read())
{
GameHistoryItem item1 = new GameHistoryItem();
item1.id = MyReader.GetInt64("id");
item1.user_id = MyReader.GetInt64("user_id");
item1.cash = MyReader.GetInt64("cash");
item1.current_cash = MyReader.GetInt64("current_cash");
item1.description = MyReader.GetString("description");
item1.game_id = MyReader.GetInt32("game_id");
item1.trans_type = MyReader.GetInt32("trans_type");
item1.time = MyReader.GetDateTime("time");
item1.before_cash = MyReader.GetInt64("before_cash");
item1.username = MyReader.GetString("username");
item1.fullname = MyReader.GetString("fullname");
lst.Add(item1);
}
//close reader cũ
MyReader.Close();
MyCommand = new MySqlCommand(sqlTotalQuery, conn);
MyCommand.Parameters.AddWithValue("zoneId", zoneId);
MyReader = MyCommand.ExecuteReader();
while (MyReader.Read())
{
totalRecord = MyReader.GetInt32("totalRecord");
}
conn.Close();
rs.data = lst;
rs.totalRecord = totalRecord;
return(rs);
}
/// <summary>
/// Select một user theo user_id
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public UserInfo SelectOne(long id)
{
var user = new UserInfo();
string sql = "select * from newDB.view_user_info where id = " + id;
MySqlConnection conn = new MySqlConnection(Constants.DBConnection);
MySqlCommand MyCommand = new MySqlCommand(sql, conn);
MySqlDataReader MyReader;
conn.Open();
MyReader = MyCommand.ExecuteReader();
while (MyReader.Read())
{
user.uid = MyReader.GetInt64("id");
user.userName = MyReader.GetString("username");
user.fullName = MyReader.GetString("fullname");
user.dateRegister = MyReader.GetDateTime("register_date");
user.isMale = MyReader.GetInt32("sex") == 1;
user.level = MyReader.GetInt32("level_id");
user.cash = MyReader.GetInt64("gameCash");
user.vcash = MyReader.GetInt64("vCash");
user.playsNumber = MyReader.GetInt32("totalGame");
user.playsWin = MyReader.GetInt32("totalWin");
if (!MyReader.IsDBNull(8))
{
user.email = MyReader.GetString("email");
}
if (!MyReader.IsDBNull(7))
{
user.phone = MyReader.GetString("mobile");
}
if (!MyReader.IsDBNull(21))
{
user.cmnd = MyReader.GetString("cmnd");
}
user.ipAddress = MyReader.GetString("ip");
user.isMobile = MyReader.GetInt32("isMobile");
user.lastLogin = MyReader.GetDateTime("last_login");
}
//close reader cũ
MyReader.Close();
conn.Close();
return(user);
}
public GameHistoryResult ExportExcel(DateTime?from, DateTime?to)
{
var rs = new GameHistoryResult();
var lst = new List <GameHistoryItem>();
int totalRecord = 0;
var sql = "SELECT a.*, b.username, b.fullname FROM [newDB].[dbo].game_history a inner join [newDB].[dbo].view_user_info b on a.user_id = b.id WHERE a.trans_type = 16";
if (from != null && to != null)
{
sql += " and time >= '" + from.Value.ToString("yyyy-MM-dd HH:mm:ss") + "' and time <= '" + to.Value.ToString("yyyy-MM-dd HH:mm:ss") + "'";
}
sql += " order by id desc";
var conn = new SqlConnection(Constants.DBConnection);
var MyCommand = new SqlCommand(sql, conn);
SqlDataReader MyReader;
conn.Open();
MyReader = MyCommand.ExecuteReader();
while (MyReader.Read())
{
var item1 = new GameHistoryItem
{
id = MyReader.GetInt32(MyReader.GetOrdinal("id")),
user_id = MyReader.GetInt32(MyReader.GetOrdinal("user_id")),
cash = MyReader.GetInt64(MyReader.GetOrdinal("cash")),
current_cash = MyReader.GetInt64(MyReader.GetOrdinal("current_cash")),
description = MyReader.GetString(MyReader.GetOrdinal("description")),
game_id = MyReader.GetInt32(MyReader.GetOrdinal("game_id")),
trans_type = MyReader.GetInt32(MyReader.GetOrdinal("trans_type")),
time = MyReader.GetDateTime(MyReader.GetOrdinal("time")),
before_cash = MyReader.GetInt64(MyReader.GetOrdinal("before_cash")),
username = MyReader.GetString(MyReader.GetOrdinal("username")),
fullname = MyReader.GetString(MyReader.GetOrdinal("fullname"))
};
item1.timestring = item1.time.Value.ToShortDateString();
lst.Add(item1);
}
//close reader cũ
MyReader.Close();
rs.data = lst;
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)
{
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);
}
/// <summary>
/// Danh sách user nạp thẻ, sms
/// </summary>
/// <param name="from"></param>
/// <param name="to"></param>
/// <param name="skip"></param>
/// <param name="take"></param>
/// <returns></returns>
public ChargeResult GetCharged(DateTime from, DateTime to, int skip, int take)
{
var rs = new ChargeResult();
var data = new List <ChargeItem>();
int totalRecord = 0;
string sql = "SELECT b.id AS 'user_id', b.username,b.fullname,'card' as 'type', cardNumber, cardSerial, cp,cardPrice, refNo, tranNo, source, dateCreated FROM portal.a_paycard a inner join portal.user b on a.username=b.id where a.cardPrice > 0 and a.dateCreated >= @date1 and dateCreated <= @date2 union all SELECT b.id AS 'user_id', b.username, b.fullname, 'sms' AS 'type', '' as 'cardNumber','' as 'cardSerial', telco as 'cp',amount as 'cardPrice', '' as 'refNo', '' as 'tranNo', '' as 'source' , responeTime as 'dateCreated' FROM portal.a_smsplus a INNER JOIN portal.user b ON a.targetUser=b.username WHERE amount > 0 AND a.responeTime >= @date3 AND a.responeTime <= @date4 ORDER BY dateCreated DESC LIMIT @skip, @take";
MySqlConnection conn = new MySqlConnection(Constants.DBConnection);
MySqlCommand MyCommand = new MySqlCommand(sql, conn);
MyCommand.Parameters.AddWithValue("date1", from.ToString(Constants.DateFormat));
MyCommand.Parameters.AddWithValue("date2", to.ToString(Constants.DateFormat));
MyCommand.Parameters.AddWithValue("date3", from.ToString(Constants.DateFormat));
MyCommand.Parameters.AddWithValue("date4", to.ToString(Constants.DateFormat));
MyCommand.Parameters.AddWithValue("skip", skip);
MyCommand.Parameters.AddWithValue("take", take);
MySqlDataReader MyReader;
conn.Open();
MyReader = MyCommand.ExecuteReader();
int stt = 1;
while (MyReader.Read())
{
ChargeItem item1 = new ChargeItem();
item1.Stt = stt;
item1.uid = MyReader.GetInt64("user_id");
item1.username = MyReader.GetString("username");
item1.fullname = MyReader.GetString("fullname");
item1.type = MyReader.GetString("type");
item1.cardNumber = MyReader.GetString("cardNumber");
item1.cardSerial = MyReader.GetString("cardSerial");
item1.telco = MyReader.GetString("cp");
item1.Price = MyReader.GetInt64("cardPrice");
item1.refNo = MyReader.GetString("refNo");
item1.tranNo = MyReader.GetString("tranNo");
item1.source = MyReader.GetString("source");
item1.time = MyReader.GetDateTime("dateCreated");
item1.timeString = item1.time.ToString("dd-MM-yyyy");
data.Add(item1);
stt++;
}
MyReader.Close();
sql = "Select count(*) as totalRecord from portal.a_paycard where cardPrice > 0 and dateCreated >= @date1 and dateCreated <= @date2";
MyCommand = new MySqlCommand(sql, conn);
MyCommand.Parameters.AddWithValue("date1", from.ToString(Constants.DateFormat));
MyCommand.Parameters.AddWithValue("date2", to.ToString(Constants.DateFormat));
MyReader = MyCommand.ExecuteReader();
while (MyReader.Read())
{
totalRecord += MyReader.GetInt32("totalRecord");
}
MyReader.Close();
sql = "Select count(*) as totalRecord from portal.a_smsplus WHERE amount > 0 AND responeTime >= @date3 AND responeTime <= @date4";
MyCommand = new MySqlCommand(sql, conn);
MyCommand.Parameters.AddWithValue("date3", from.ToString(Constants.DateFormat));
MyCommand.Parameters.AddWithValue("date4", to.ToString(Constants.DateFormat));
MyReader = MyCommand.ExecuteReader();
while (MyReader.Read())
{
totalRecord += MyReader.GetInt32("totalRecord");
}
MyReader.Close();
conn.Close();
rs.data = data;
rs.totalRecord = totalRecord;
return(rs);
}
public List <CardItem> GetAllCard(out int total, int skip, int take, int used = 0, int telcoId = 0, int value = 0, string seri = "")
{
var rs = new List <CardItem>();
var sql = "SELECT * FROM [portal].[dbo].exchangeCardInfo ";
var sqlTotalQuery = "SELECT Count(id) as totalRecord FROM [portal].[dbo].exchangeCardInfo";
sql += " where used = " + used;
sqlTotalQuery += " where used = " + used;
if (telcoId > 0)
{
sql += " and telcoId = " + telcoId;
sqlTotalQuery += " and telcoId = " + telcoId;
}
if (value > 0)
{
sql += " and value = " + value;
sqlTotalQuery += " and value = " + value;
}
if (!string.IsNullOrEmpty(seri))
{
sql += " and serial = '" + seri + "'";
sqlTotalQuery += " and serial = '" + value + "'";
}
sql += " ORDER BY id desc OFFSET " + skip + " ROWS FETCH NEXT " + take + " ROWS ONLY ";
var conn = new SqlConnection(Constants.DBConnection);
var MyCommand = new SqlCommand(sql, conn);
SqlDataReader MyReader;
conn.Open();
MyReader = MyCommand.ExecuteReader();
while (MyReader.Read())
{
var u = new CardItem
{
id = MyReader.GetInt32(MyReader.GetOrdinal("id")),
serial = MyReader.GetString(MyReader.GetOrdinal("serial")),
//cardNo = MyReader.GetString(MyReader.GetOrdinal("cardNo"),
dateInput = MyReader.GetDateTime(MyReader.GetOrdinal("dateInput")),
dateInputString = MyReader.GetDateTime(MyReader.GetOrdinal("dateInput")).ToShortDateString(),
dateExpiredString = MyReader.GetDateTime(MyReader.GetOrdinal("dateExpired")).ToShortDateString(),
dateExpired = MyReader.GetDateTime(MyReader.GetOrdinal("dateExpired")),
used = MyReader.GetByte(MyReader.GetOrdinal("used")),
value = MyReader.GetInt32(MyReader.GetOrdinal("value")),
telcoId = MyReader.GetInt32(MyReader.GetOrdinal("telcoId"))
};
if (used > 0)
{
u.dateUse = MyReader.GetDateTime(MyReader.GetOrdinal("dateUse"));
u.dateUseString = MyReader.GetDateTime(MyReader.GetOrdinal("dateUse")).ToShortDateString();
}
rs.Add(u);
}
MyReader.Close();
MyCommand = new SqlCommand(sqlTotalQuery, conn);
MyReader = MyCommand.ExecuteReader();
total = 0;
while (MyReader.Read())
{
total = MyReader.GetInt32(MyReader.GetOrdinal("totalRecord"));
}
MyReader.Close();
conn.Close();
return(rs);
}
public List <PaymentItem> GetPayment(out int total, int skip, int take, int uid = 0, string username = "", string date = "", string status = "")
{
var rs = new List <PaymentItem>();
var sql = "SELECT us.username, us.fullname, pay.id, pay.buyer_uid, pay.buyer_fullname, pay.buyer_mobile, pay.total_amount, pay.payment_method, pay.bank_code, pay.transaction_status, pay.transaction_id, pay.time_request, pay.time_receive, pay.token FROM [portal].[dbo].a_bankcharge pay " +
" LEFT JOIN [portal].[dbo].user us ON pay.buyer_uid = us.id where pay.id > 0 ";
var sqlTotalQuery = "SELECT Count(pay.id) as totalRecord FROM [portal].[dbo].a_bankcharge pay LEFT JOIN [portal].[dbo].user us ON pay.buyer_uid = us.id where pay.id > 0 ";
if (uid > 0)
{
sql += " AND pay.buyer_uid = " + uid;
sqlTotalQuery += " AND pay.buyer_uid = " + uid;
}
if (!string.IsNullOrEmpty(username))
{
sql += " AND (LOWER(pay.buyer_mobile) LIKE '%" + username.ToLower() + "%' or LOWER(pay.buyer_fullname) LIKE '%" + username.ToLower() + "%' or LOWER(us.username) LIKE '%" + username.ToLower() + "%' or LOWER(us.fullname) LIKE '%" + username.ToLower() + "%')";
sqlTotalQuery += " AND (LOWER(pay.buyer_mobile) LIKE '%" + username.ToLower() + "%' or LOWER(pay.buyer_fullname) LIKE '%" + username.ToLower() + "%' or LOWER(us.username) LIKE '%" + username.ToLower() + "%' or LOWER(us.fullname) LIKE '%" + username.ToLower() + "%')";
}
if (!string.IsNullOrEmpty(date))
{
var datex = Convert.ToDateTime(date).ToString(Constants.DateFormat);
sql += " AND time_request >= '" + datex + "'";
sqlTotalQuery += " and time_request >= '" + datex + "'";
}
if (!string.IsNullOrEmpty(status))
{
if (status == "00")
{
sql += " AND transaction_status = '00'";
sqlTotalQuery += " and transaction_status = '00'";
}
else
{
sql += " AND transaction_status <> '00'";
sqlTotalQuery += " and transaction_status <> '00'";
}
}
sql += " ORDER BY pay.id desc OFFSET " + skip + " ROWS FETCH NEXT " + take + " ROWS ONLY";
SqlConnection conn = new SqlConnection(Constants.DBConnection);
SqlCommand MyCommand = new SqlCommand(sql, conn);
SqlDataReader MyReader;
conn.Open();
MyReader = MyCommand.ExecuteReader();
while (MyReader.Read())
{
var u = new PaymentItem();
u.id = MyReader.GetInt32(MyReader.GetOrdinal("id"));
u.buyer_mobile = MyReader.GetString(MyReader.GetOrdinal("buyer_mobile"));
u.total_amount = MyReader.GetInt32(MyReader.GetOrdinal("total_amount"));
u.buyer_mobile = MyReader.GetString(MyReader.GetOrdinal("buyer_mobile"));
u.buyer_fullname = MyReader.GetString(MyReader.GetOrdinal("buyer_fullname"));
u.payment_method = MyReader.GetString(MyReader.GetOrdinal("payment_method"));
u.bank_code = MyReader.GetString(MyReader.GetOrdinal("bank_code"));
u.transaction_status = MyReader.GetString(MyReader.GetOrdinal("transaction_status"));
u.token = MyReader.GetString(MyReader.GetOrdinal("token"));
// index start 0
if (!MyReader.IsDBNull(3))
{
u.buyer_uid = MyReader.GetInt32(MyReader.GetOrdinal("buyer_uid"));
}
if (!MyReader.IsDBNull(11))
{
u.time_request = MyReader.GetDateTime(MyReader.GetOrdinal("time_request"));
u.time_request_string = u.time_request.ToShortDateString();
}
if (!MyReader.IsDBNull(12))
{
u.time_receive = MyReader.GetDateTime(MyReader.GetOrdinal("time_receive"));
u.time_receive_string = u.time_receive.ToShortDateString();
}
if (!MyReader.IsDBNull(10))
{
u.transaction_id = MyReader.GetString(MyReader.GetOrdinal("transaction_id"));
}
if (!MyReader.IsDBNull(0))
{
u.username = MyReader.GetString(MyReader.GetOrdinal("username"));
}
if (!MyReader.IsDBNull(1))
{
u.fullname = MyReader.GetString(MyReader.GetOrdinal("fullname"));
}
rs.Add(u);
}
MyReader.Close();
MyCommand = new SqlCommand(sqlTotalQuery, conn);
MyReader = MyCommand.ExecuteReader();
total = 0;
while (MyReader.Read())
{
total = MyReader.GetInt32(MyReader.GetOrdinal("totalRecord"));
}
conn.Close();
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);
}
public PaymentItem PaymentDetail(int id)
{
var rs = new PaymentItem();
var sql = "SELECT us.username, us.fullname, pay.id, pay.buyer_uid, pay.buyer_fullname, pay.buyer_mobile, pay.total_amount, pay.payment_method, pay.bank_code, pay.transaction_status, pay.transaction_id, pay.time_request, pay.time_receive, pay.token FROM [portal].[dbo].a_bankcharge pay " +
" LEFT JOIN [portal].[dbo].user us ON pay.buyer_uid = us.id where pay.id = " + id;
var conn = new SqlConnection(Constants.DBConnection);
var MyCommand = new SqlCommand(sql, conn);
SqlDataReader MyReader;
conn.Open();
MyReader = MyCommand.ExecuteReader();
while (MyReader.Read())
{
var u = new PaymentItem
{
id = MyReader.GetInt32(MyReader.GetOrdinal("id")),
buyer_mobile = MyReader.GetString(MyReader.GetOrdinal("buyer_mobile")),
total_amount = MyReader.GetInt32(MyReader.GetOrdinal("total_amount"))
};
u.buyer_mobile = MyReader.GetString(MyReader.GetOrdinal("buyer_mobile"));
u.buyer_fullname = MyReader.GetString(MyReader.GetOrdinal("buyer_fullname"));
u.payment_method = MyReader.GetString(MyReader.GetOrdinal("payment_method"));
u.bank_code = MyReader.GetString(MyReader.GetOrdinal("bank_code"));
u.transaction_status = MyReader.GetString(MyReader.GetOrdinal("transaction_status"));
u.token = MyReader.GetString(MyReader.GetOrdinal("token"));
// index start 0
if (!MyReader.IsDBNull(3))
{
u.buyer_uid = MyReader.GetInt32(MyReader.GetOrdinal("buyer_uid"));
}
if (!MyReader.IsDBNull(11))
{
u.time_request = MyReader.GetDateTime(MyReader.GetOrdinal("time_request"));
u.time_request_string = u.time_request.ToShortDateString();
}
if (!MyReader.IsDBNull(12))
{
u.time_receive = MyReader.GetDateTime(MyReader.GetOrdinal("time_receive"));
u.time_receive_string = u.time_receive.ToShortDateString();
}
if (!MyReader.IsDBNull(10))
{
u.transaction_id = MyReader.GetString(MyReader.GetOrdinal("transaction_id"));
}
if (!MyReader.IsDBNull(0))
{
u.username = MyReader.GetString(MyReader.GetOrdinal("username"));
}
if (!MyReader.IsDBNull(1))
{
u.fullname = MyReader.GetString(MyReader.GetOrdinal("fullname"));
}
rs = u;
}
MyReader.Close();
conn.Close();
return(rs);
}
/// <summary>
/// Danh sách user, sắp xếp theo đăng ký mới nhất
/// </summary>
/// <param name="id"></param>
/// <param name="username"></param>
/// <param name="fullname"></param>
/// <param name="phone"></param>
/// <param name="email"></param>
/// <param name="cmnd"></param>
/// <param name="regFrom"></param>
/// <param name="regTo"></param>
/// <param name="mobile"></param>
/// <param name="active"></param>
/// <param name="limit"></param>
/// <param name="take"></param>
/// <returns></returns>
public UserInfoSearchResult Search(long id, string username, string fullname, string phone, string email, string cmnd, DateTime regFrom, DateTime regTo, int mobile, bool?active, int limit, int take)
{
UserInfoSearchResult rs = new UserInfoSearchResult();
var lst = new List <UserInfo>();
int totalRecord = 0;
string sql = "select * from newDB.view_user_info where register_date >= '" + regFrom.ToString(Constants.DateFormat) + "' and register_date <= '" + regTo.ToString(Constants.DateFormat) + "'";
string sqlTotalQuery = "select count(*) as totalRecord from newDB.view_user_info where register_date >= '" + regFrom.ToString(Constants.DateFormat) + "' and register_date <= '" + regTo.ToString(Constants.DateFormat) + "'";
if (id > 0)
{
sql += " and id = " + id.ToString();
sqlTotalQuery += " and id = " + id.ToString();
}
if (!String.IsNullOrEmpty(username))
{
sql += " and username like '%" + username + "%'";
sqlTotalQuery += " and username like '%" + username + "%'";
}
if (!String.IsNullOrEmpty(fullname))
{
sql += " and fullname like '%" + fullname + "%'";
sqlTotalQuery += " and fullname like '%" + fullname + "%'";
}
if (!String.IsNullOrEmpty(phone))
{
sql += " and mobile like '%" + phone + "%'";
sqlTotalQuery += " and mobile like '%" + phone + "%'";
}
if (!String.IsNullOrEmpty(email))
{
sql += " and email like '%" + email + "%'";
sqlTotalQuery += " and email like '%" + email + "%'";
}
if (!String.IsNullOrEmpty(cmnd))
{
sql += " and cmnd like '%" + cmnd + "%'";
sqlTotalQuery += " and cmnd like '%" + cmnd + "%'";
}
if (mobile > 0)
{
sql += " and isMobile = " + mobile.ToString();
sqlTotalQuery += " and isMobile = " + mobile.ToString();
}
if (active != null)
{
if (Convert.ToBoolean(active))
{
sql += " and is_active = 1";
sqlTotalQuery += " and is_active = 1";
}
else
{
sql += " and is_active = 2";
sqlTotalQuery += " and is_active = 2";
}
}
sql += " order by id desc";
sql += " limit " + limit + ", " + take;
MySqlConnection conn = new MySqlConnection(Constants.DBConnection);
MySqlCommand MyCommand = new MySqlCommand(sql, conn);
MySqlDataReader MyReader;
conn.Open();
MyReader = MyCommand.ExecuteReader();
while (MyReader.Read())
{
UserInfo user = new UserInfo();
user.uid = MyReader.GetInt64("id");
user.userName = MyReader.GetString("username");
user.fullName = MyReader.GetString("fullname");
user.dateRegister = MyReader.GetDateTime("register_date");
user.isMale = MyReader.GetInt32("sex") == 1;
user.level = MyReader.GetInt32("level_id");
user.cash = MyReader.GetInt64("gameCash");
user.vcash = MyReader.GetInt64("vCash");
user.playsNumber = MyReader.GetInt32("totalGame");
user.playsWin = MyReader.GetInt32("totalWin");
if (!MyReader.IsDBNull(8))
{
user.email = MyReader.GetString("email");
}
if (!MyReader.IsDBNull(7))
{
user.phone = MyReader.GetString("mobile");
}
if (!MyReader.IsDBNull(21))
{
user.cmnd = MyReader.GetString("cmnd");
}
user.ipAddress = MyReader.GetString("ip");
user.isMobile = MyReader.GetInt32("isMobile");
user.lastLogin = MyReader.GetDateTime("last_login");
lst.Add(user);
}
//close reader cũ
MyReader.Close();
MyCommand = new MySqlCommand(sqlTotalQuery, conn);
MyReader = MyCommand.ExecuteReader();
while (MyReader.Read())
{
totalRecord = MyReader.GetInt32("totalRecord");
}
conn.Close();
rs.data = lst;
rs.totalRecord = totalRecord;
return(rs);
}
private void comboBox_misCultivos_SelectedIndexChanged(object sender, EventArgs e)
{
Random random = new Random();
int num = random.Next(100);
textBox1.Text = num.ToString() + " %";
DateTime today = DateTime.Today;
double diff = 0;
double porcentaje = 0;
String rec = "";
MySqlConnection conexion = Conectar();
String sentencia = "SELECT fecha_siembra FROM mis_cultivos WHERE nombre = '" + comboBox_misCultivos.Text + "'";
MySqlCommand comando = new MySqlCommand(sentencia, conexion);
conexion.Open();
comando.ExecuteNonQuery();
MySqlDataReader MyReader;
MyReader = comando.ExecuteReader();
while (MyReader.Read())
{
var dateString = MyReader.GetDateTime(MyReader.GetOrdinal("fecha_siembra")).ToString("yyyy/MM/dd");
DateTime myDate = DateTime.Parse(dateString);
diff = (today - myDate).TotalDays;
}
MyReader.Close();
String sentencia2 = "UPDATE mis_cultivos SET dias_transcurridos = " + diff + " WHERE nombre = '" + comboBox_misCultivos.Text + "'";
MySqlCommand comando2 = new MySqlCommand(sentencia2, conexion);
comando2.ExecuteNonQuery();
String sentencia3 = "SELECT dias_desarrollo FROM mis_cultivos WHERE nombre = '" + comboBox_misCultivos.Text + "'";
MySqlCommand comando3 = new MySqlCommand(sentencia3, conexion);
comando3.ExecuteNonQuery();
MyReader = comando3.ExecuteReader();
while (MyReader.Read())
{
double dias = MyReader.GetDouble("dias_desarrollo");
porcentaje = (diff * 100) / dias;
}
porcentaje = Math.Round(porcentaje, 2);
int porcentaje_round = ((int)Math.Round(porcentaje / 10.0)) * 10;
MyReader.Close();
String sentencia4 = "SELECT recomendacion FROM " + comboBox_misCultivos.Text + " WHERE crecimiento = " + porcentaje_round + "";
MySqlCommand comando4 = new MySqlCommand(sentencia4, conexion);
comando4.ExecuteNonQuery();
MyReader = comando4.ExecuteReader();
while (MyReader.Read())
{
rec = MyReader.GetString(0);
}
MyReader.Close();
String sentencia5 = "SELECT imagen FROM " + comboBox_misCultivos.Text + " WHERE crecimiento = " + porcentaje_round + "";
MySqlCommand comando5 = new MySqlCommand(sentencia5, conexion);
comando5.ExecuteNonQuery();
MyReader = comando5.ExecuteReader();
while (MyReader.Read())
{
var bytes = (byte[])MyReader[0];
using (MemoryStream ms = new MemoryStream(bytes))
{
pictureBox_imagenCrecimiento.Image = Image.FromStream(ms);
}
}
MyReader.Close();
conexion.Close();
descripcion.Text = "Su cultivo tiene " + diff.ToString() + " días de haberse sembrado, lo que corresponde a " + porcentaje + "% de su estación de crecimiento. " + rec + " Su cultivo debería parecerse a la siguiente imagen: ";
// pictureBox_imagenCrecimiento.Show();
}
