public List <DEBIT_HIS> DataToDebit(DataTable dt)
{
List <DEBIT_HIS> list = new List <DEBIT_HIS>();
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
List <USER> lu = new T_USER_SQL(connstring).Get_User(dt.Rows[i]["U_SYSID"].ToString());
DEBIT_HIS d = new DEBIT_HIS
{
U_SYSID = dt.Rows[i]["U_SYSID"].ToString(),
D_AMOUNT = dt.Rows[i]["D_AMOUNT"].ToString(),
D_REASON = dt.Rows[i]["D_REASON"].ToString(),
D_ISDEL = dt.Rows[i]["D_ISDEL"].ToString(),
D_DATE = dt.Rows[i]["D_DATE"].ToString(),
D_SYSID = dt.Rows[i]["D_SYSID"].ToString(),
USER = lu.Count > 0 ? lu[0] : new USER(),
NUM = dt.Rows[i]["NUM"].ToString()
};
list.Add(d);
}
}
return(list);
}
public int Insert(DEBIT_HIS d)
{
using (SqlConnection conn = new SqlConnection(connstring))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = string.Format("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;SELECT MAX(D_SYSID) FROM T_DEBIT_HIS WHERE SUBSTRING(D_SYSID,1,8)='{0}';", DateTime.Now.ToString("yyyyMMdd"));
string d_sysid = string.Empty;
d_sysid = cmd.ExecuteScalar().ToString();
if (string.IsNullOrEmpty(d_sysid))
{
d_sysid = DateTime.Now.ToString("yyyyMMdd") + "5000001";
}
else
{
d_sysid = (Convert.ToInt64(d_sysid) + 1).ToString();
}
cmd.CommandText = string.Format(@"INSERT INTO T_DEBIT_HIS (U_SYSID,D_REASON,D_AMOUNT,D_DATE,D_SYSID) VALUES ('{0}',N'{1}','{2}','{3}','{4}')",
d.U_SYSID, d.D_REASON, d.D_AMOUNT, d.D_DATE, d_sysid);
return(cmd.ExecuteNonQuery());
}
}
}
public int Debit_Amount(DEBIT_HIS d)
{
using (SqlConnection conn = new SqlConnection(connstring))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = string.Format(@"UPDATE T_USER SET U_BALANCE=U_BALANCE-'{0}' WHERE U_SYSID='{1}' AND U_ISDEL='1' ", d.D_AMOUNT, d.U_SYSID);
return(cmd.ExecuteNonQuery());
}
}
}
/// <summary>
/// 更新
/// </summary>
/// <param name="d"></param>
/// <returns></returns>
public int Update(DEBIT_HIS d)
{
using (SqlConnection conn = new SqlConnection(connstring))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = string.Format(@"UPDATE T_DEBIT_HIS SET D_REASON=N'{0}',D_AMOUNT='{1}',D_DATE='{2}' WHERE D_SYSID='{3}' AND D_ISDEL='1' ", d.D_REASON, d.D_AMOUNT, d.D_DATE, d.D_SYSID);
return(cmd.ExecuteNonQuery());
}
}
}
private void button1_Click(object sender, EventArgs e)
{
try
{
if (MessageBox.Show("查询全部信息消费5元", "查询提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Information) == DialogResult.OK)
{
DEBIT_HIS d = new DEBIT_HIS();
d.U_SYSID = logonUser.U_SYSID;
d.D_REASON = "平台内借款信息查询";
d.D_AMOUNT = "5";
d.D_DATE = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss");
string balance = user_sql.QueryByWhere_XP(string.Format(" AND U_SYSID='{0}'", logonUser.U_SYSID))[0].U_BALANCE;
if (Convert.ToInt32(balance) < 5)
{
MessageBox.Show("余额不足", "查询提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
if (debit_his_sql.Insert(d) == 1)
{
user_sql.Debit_Amount(d);
logonUser.U_BALANCE = (Convert.ToInt32(balance) - 5).ToString();
bindData();
}
else
{
MessageBox.Show("失败，请重试", "查询信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
}
catch (Exception e1)
{
MessageBox.Show(e1.Message, "报错", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
