/// <summary>
/// 更新库存方法
/// </summary>
/// <param name="num">用户输入</param>
/// <param name="key">主键</param>
/// <param name="b">true为增加，false为减少</param>
private void updateReserve(double num, int key)
{
if (num == 0)
{
return;
}
string sql_SelectReserve = "SELECT d_reserve FROM tb_drug WHERE d_id=" + key;
double reserve = (double)MySqlTools.ExecuteScalar(sql_SelectReserve);
if (reserve - num < 0)
{
MessageBox.Show(" 该药品剩余库存为:" + reserve + "\n 库存不足,请尽快补货！", "温馨提示");
}
else
{
string sql_updateReserve = string.Format("UPDATE tb_drug SET d_reserve={0} WHERE d_id={1}", reserve - num, key);
// sql_select_r_price = "SELECT d_r_price FROM tb_drug WHERE d_id=" + key;
//double money =num*(double)MySqlTools.ExecuteScalar(sql_select_r_price);
if (MySqlTools.ExecuteNonQuery(sql_updateReserve) == 1)
{
MessageBox.Show("出库成功！", "温馨提示");
}
else
{
MessageBox.Show("出库失败！", "温馨提示");
}
}
//}
}
/// <summary>
/// 销售页面查询所有并绑定
/// </summary>
protected void BindAll_X()
{
string sql_select_drug_X = "SELECT d_id,d_name,u_name,d_spec,d_reserve,d_r_price FROM tb_drug INNER JOIN tb_unit ON d_unit = u_id WHERE d_unit = u_id";
DataSet ds = MySqlTools.GetDataSet(sql_select_drug_X);
dataGridView_X.DataSource = ds.Tables[0];
}
private void WriteMemoryToDb()
{
using (DataConnection db = MySqlTools.CreateDataConnection(
ConfigLoadingManager.GetInstance()
.GetConfig().Database.GetConnectionString()))
{
try
{
db.BeginTransaction(IsolationLevel.Serializable);
foreach (var p in ArticleCache.Memory.Values)
{
db.InsertOrReplace(new ArticleTable
{
Id = p.Id,
Time = (ulong)p.Time,
Info = JsonConvert.SerializeObject(p)
});
}
}
finally
{
db.CommitTransaction();
}
}
}
static private void UpdateToSource(uint articleId, ArticleInfo articleInfo)
{
var newDbElement = new ArticleTable
{
Id = articleInfo.Id,
Time = (ulong)articleInfo.Time,
Info = JsonConvert.SerializeObject(articleInfo)
};
using (DataConnection db = MySqlTools.CreateDataConnection(
ConfigLoadingManager.GetInstance()
.GetConfig().Database.GetConnectionString()))
{
try
{
db.BeginTransaction(IsolationLevel.Serializable);
var query = (from p in db.GetTable <ArticleTable>()
where p.Id == newDbElement.Id
select p);
var isExists = query.Count() != 0;
if (!isExists)
{
return;
}
db.InsertOrReplace(newDbElement);
}
finally
{
db.CommitTransaction();
}
}
}
/// <summary>
/// 销售页面按条件绑定
/// </summary>
private void BindByTrim_X()
{
string d_name = string.Format("'%{0}%'", textBox_select_X.Text);
string sql_select_by_d_name = "SELECT d_id,d_name,u_name,d_spec,d_reserve,d_r_price FROM tb_drug INNER JOIN tb_unit ON d_unit = u_id WHERE d_unit = u_id AND d_name LIKE " + d_name;
DataSet ds = MySqlTools.GetDataSet(sql_select_by_d_name);
dataGridView_X.DataSource = ds.Tables[0];
}
static private void RemoveFromSource(uint articleId)
{
using (DataConnection db = MySqlTools.CreateDataConnection(
ConfigLoadingManager.GetInstance()
.GetConfig().Database.GetConnectionString()))
{
db.Delete(new ArticleTable {
Id = (uint)articleId
});
}
}
/// <summary>
/// 绑定单位到comboBox
/// </summary>
private void bindUnit()
{
string sql = "SELECT * from tb_unit";
DataSet ds = MySqlTools.GetDataSet(sql);
//绑定数据源
cb_unit.DataSource = ds.Tables[0];
//值为u_id
cb_unit.ValueMember = "u_id";
//属性为u_name
cb_unit.DisplayMember = "u_name";
}
/// <summary>
/// 修改密码按钮
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void bun_ch_Click(object sender, EventArgs e)
{
string sql = string.Format("UPDATE tb_pwd SET pwd='{0}' where id=1", tb_pwdCh.Text);
if ((int)MySqlTools.ExecuteNonQuery(sql) == 1)
{
MessageBox.Show("密码更改成功，请牢记！\n" + tb_pwdCh.Text, "温馨提示");
}
else
{
MessageBox.Show("密码更改失败！", "温馨提示");
}
}
private static void Init()
{
host = MySqlSettings.HOST;
port = MySqlSettings.PORT;
acc = MySqlSettings.ACCOUNT;
pwd = MySqlSettings.PASSWORD;
db = MySqlSettings.DATABASE;
//获取当前实例
instance = EditorWindow.GetWindow <MySqlTools>();
//初始化
pathRoot = Application.dataPath;
scrollPos = new Vector2(instance.position.x, instance.position.y + 75);
}
//Cache callbacks↓
private void LoadAllToCache()
{
using (DataConnection db = MySqlTools.CreateDataConnection(
ConfigLoadingManager.GetInstance()
.GetConfig().Database.GetConnectionString()))
{
var query = from p in db.GetTable <ArticleTable>()
select p;
foreach (var p in query)
{
var article = JsonConvert.DeserializeObject <ArticleInfo>(p.Info);
article.Id = p.Id;
article.Time = (long)p.Time;
this.ArticleCache.Memory.TryAdd(article.Id, article);
}
}
}
/// <summary>
/// 库存页面查询所有并绑定
/// </summary>
protected void BindAll_K()
{
string sql_select_drug_K = "SELECT d_id,d_name,u_name,d_spec,d_origin,d_lot_num,d_reserve,d_w_price,d_r_price FROM tb_drug INNER JOIN tb_unit ON d_unit = u_id WHERE d_unit = u_id";
DataSet ds = MySqlTools.GetDataSet(sql_select_drug_K);
DataTable dt = ds.Tables[0];
//添加一列“tb_progit”
dt.Columns.Add("tb_progit");
//为该列的每一行赋值
for (int i = 0; i < dt.Rows.Count; i++)
{
dt.Rows[i]["tb_progit"] = Convert.ToDouble(dt.Rows[i][8]) - Convert.ToDouble(dt.Rows[i][7]);
}
dataGridView_K.DataSource = dt;
getSum();
}
/// <summary>
/// 登录按钮
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btn_login_Click(object sender, EventArgs e)
{
string pwd = tb_pwd.Text;
string sql = string.Format("SELECT * FROM tb_pwd WHERE pwd='{0}'", pwd);
try
{
if ((int)MySqlTools.ExecuteScalar(sql) == 1)
{
panl_Login.Visible = false;
}
}
catch (Exception)
{
MessageBox.Show("您的密码输入有误，请重新输入!", "温馨提示");
}
}
/// <summary>
/// 删除按钮事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button3_Click(object sender, EventArgs e)
{
string caption = "温馨提示";
string message = "是否删除该条数据？";
MessageBoxButtons btn = MessageBoxButtons.YesNo;
DialogResult result = new DialogResult();
result = MessageBox.Show(message, caption, btn);
if (result == System.Windows.Forms.DialogResult.Yes)
{
string sql = "delete from tb_drug where d_id=" + KEY;
if (MySqlTools.ExecuteNonQuery(sql) > 0)
{
MessageBox.Show("成功删除该条记录！", "温馨提示");
BindAll_K();
}
}
}
/// <summary>
/// 库存页面按条件绑定
/// </summary>
private void BindByTrim_K()
{
string d_name = string.Format("'%{0}%'", textBox_select_K.Text);
string sql_select_by_d_name = "SELECT d_id, d_name, u_name, d_spec, d_origin, d_lot_num, d_reserve, d_w_price, d_r_price FROM tb_drug INNER JOIN tb_unit ON d_unit = u_id WHERE d_unit = u_id AND d_name LIKE " + d_name;
DataSet ds = MySqlTools.GetDataSet(sql_select_by_d_name);
DataTable dt = ds.Tables[0];
//添加一列“tb_progit”
dt.Columns.Add("tb_progit");
//为该列的每一行赋值
for (int i = 0; i < dt.Rows.Count; i++)
{
dt.Rows[i]["tb_progit"] = Convert.ToDouble(dt.Rows[i][8]) - Convert.ToDouble(dt.Rows[i][7]);
}
dataGridView_K.DataSource = dt;
getSum();
}
private void button10_Click(object sender, EventArgs e)
{
string caption = "温馨提示";
string message = "是否删除该记录？";
MessageBoxButtons btn = MessageBoxButtons.YesNo;
DialogResult result = new DialogResult();
result = MessageBox.Show(message, caption, btn);
if (result == System.Windows.Forms.DialogResult.Yes)
{
DateTime dt = dateTimePicker1.Value;
string time = dt.ToLongDateString().ToString();
string sql = "delete from tb_order";
if (MySqlTools.ExecuteNonQuery(sql) > 0)
{
MessageBox.Show("成功删除该记录！", "温馨提示");
BindAndShow();
}
}
}
static private ArticleInfo GetFromSource(uint articleId)
{
using (DataConnection db = MySqlTools.CreateDataConnection(
ConfigLoadingManager.GetInstance()
.GetConfig().Database.GetConnectionString()))
{
var query = from p in db.GetTable <ArticleTable>()
where p.Id == articleId
select p;
if (query.Count() == 0)
{
return(null);
}
var columns = query.First();
var article = JsonConvert.DeserializeObject <ArticleInfo>(columns.Info);
article.Id = columns.Id;
article.Time = (long)columns.Time;
return(article);
}
}
/// <summary>
/// 销售记录查询
/// </summary>
protected void BindAndShow()
{
DateTime dt = dateTimePicker1.Value;
string time = dt.ToLongDateString().ToString();
string sql = string.Format("SELECT o_id,o_name,o_num,o_r_price,o_money FROM tb_order where o_time='{0}'", time);
DataSet ds = MySqlTools.GetDataSet(sql);
DataTable dataTable = ds.Tables[0];
dataGridView_M.DataSource = dataTable;
//求和
double sum_m = 0;
double sum_r = 0;
for (int i = 0; i < dataGridView_M.Rows.Count; i++)
{
sum_m += Convert.ToDouble(dataGridView_M.Rows[i].Cells["利润"].Value);
sum_r += Convert.ToDouble(dataGridView_M.Rows[i].Cells["售价"].Value);
}
lb_r.Text = sum_r.ToString() + "元";
lb_m.Text = sum_m.ToString() + "元";
}
private void GenerateRanking(uint id)
{
var rankingVersion = RankingVersionPivot.Get(id);
if (rankingVersion == null)
{
throw new ArgumentException(Messages.RankingRulesetNotFoundException, nameof(id));
}
// Gets the latest monday with a computed ranking.
var startDate = MySqlTools.ExecuteScalar(GlobalAppConfig.GetConnectionString(),
"SELECT MAX(date) FROM ranking WHERE version_id = @version",
RankingVersionPivot.OPEN_ERA_BEGIN,
new MySqlParameter("@version", MySqlDbType.UInt32)
{
Value = id
});
// Monday one day after the latest tournament played (always a sunday).
var dateStop = (EditionPivot.GetLatestEditionDateEnding() ?? startDate).AddDays(1);
// Loads matches from the previous year.
SqlMapper.LoadMatches((uint)startDate.Year - 1);
using (var sqlConnection = new MySqlConnection(GlobalAppConfig.GetConnectionString()))
{
sqlConnection.Open();
using (var sqlCommand = sqlConnection.CreateCommand())
{
sqlCommand.CommandText = MySqlTools.GetSqlInsertStatement("ranking", new List <string>
{
"player_id", "date", "points", "ranking", "version_id", "editions"
});
sqlCommand.Parameters.Add("@player_id", MySqlDbType.UInt32);
sqlCommand.Parameters.Add("@date", MySqlDbType.DateTime);
sqlCommand.Parameters.Add("@points", MySqlDbType.UInt32);
sqlCommand.Parameters.Add("@ranking", MySqlDbType.UInt32);
sqlCommand.Parameters.Add("@version_id", MySqlDbType.UInt32);
sqlCommand.Parameters.Add("@editions", MySqlDbType.UInt32);
sqlCommand.Prepare();
// Static.
sqlCommand.Parameters["@version_id"].Value = id;
// Puts in cache the triplet player/edition/points, no need to recompute each week.
var cachePlayerEditionPoints = new Dictionary <KeyValuePair <PlayerPivot, EditionPivot>, uint>();
// For each week until latest date.
startDate = startDate.AddDays(7);
while (startDate <= dateStop)
{
// Loads matches from the current year (do nothing if already done).
SqlMapper.LoadMatches((uint)startDate.Year);
var playersRankedThisWeek = rankingVersion.ComputePointsForPlayersInvolvedAtDate(startDate, cachePlayerEditionPoints);
// Static for each player.
sqlCommand.Parameters["@date"].Value = startDate;
// Inserts each player.
int rank = 1;
foreach (var player in playersRankedThisWeek.Keys)
{
sqlCommand.Parameters["@player_id"].Value = player.Id;
sqlCommand.Parameters["@points"].Value = playersRankedThisWeek[player].Item1;
sqlCommand.Parameters["@editions"].Value = playersRankedThisWeek[player].Item2;
sqlCommand.Parameters["@ranking"].Value = rank;
sqlCommand.ExecuteNonQuery();
rank++;
}
startDate = startDate.AddDays(7);
}
}
}
}
