public static RM_keshiyewufei AddRM_keshiyewufei(RM_keshiyewufei rM_keshiyewufei)
{
string sql =
"INSERT RM_keshiyewufei (Depname, BQJE, SQJE, ZJJE, KYJE, Remark, Remark1,LSJL)" +
"VALUES (@Depname, @BQJE, @SQJE, @ZJJE, @KYJE, @Remark, @Remark1,@LSJL)";
sql += " ; SELECT SCOPE_IDENTITY()";
try
{
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@Depname", rM_keshiyewufei.Depname),
new SqlParameter("@BQJE", rM_keshiyewufei.BQJE),
new SqlParameter("@SQJE", rM_keshiyewufei.SQJE),
new SqlParameter("@ZJJE", rM_keshiyewufei.ZJJE),
new SqlParameter("@KYJE", rM_keshiyewufei.KYJE),
new SqlParameter("@Remark", rM_keshiyewufei.Remark),
new SqlParameter("@Remark1", rM_keshiyewufei.Remark1),
new SqlParameter("@LSJL", rM_keshiyewufei.LSJL)
};
string IdStr = DBUnity.ExecuteScalar(CommandType.Text, sql, para);
int newId = Convert.ToInt32(IdStr);
return GetRM_keshiyewufeiByKSID(newId);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
throw e;
}
}
public static bool ModifyRM_keshiyewufei(RM_keshiyewufei rM_keshiyewufei)
{
return RM_keshiyewufeiService.ModifyRM_keshiyewufei(rM_keshiyewufei);
}
public static bool DeleteRM_keshiyewufei(RM_keshiyewufei rM_keshiyewufei)
{
return RM_keshiyewufeiService.DeleteRM_keshiyewufei(rM_keshiyewufei);
}
public static RM_keshiyewufei AddRM_keshiyewufei(RM_keshiyewufei rM_keshiyewufei)
{
return RM_keshiyewufeiService.AddRM_keshiyewufei(rM_keshiyewufei);
}
protected void btnIn_Click(object sender, EventArgs e)
{
try
{
string fileName1 = this.fileSelect.PostedFile.FileName;
string name = this.GetFileName(fileName1);
string path = base.Server.MapPath("~/files") + @"\" + name;
this.fileSelect.SaveAs(path);
hidpath.Value = path;
string sqlstr = "select * from RM_keshiyewufei where Remark1='" + DateTime.Now.Year + "' and Remark=1";
DataTable dtsql = DBUnity.AdapterToTab(sqlstr);
if (dtsql.Rows.Count > 0)
{
var s = "if(confirm('是否覆盖原有的数据！点击确定覆盖数据，点击取消取消操作！')){0};else {1};";
ScriptManager.RegisterStartupScript(this, typeof(verify_juzhangjijin), "",
string.Format(s, this.ClientScript.GetPostBackEventReference(this, "是"), this.ClientScript.GetPostBackEventReference(this, "否")),
true);
}
else
{
DataTable dt = ImportExcelFile(path);
string sqlstr11 = "select * from RM_keshiyewufei where Remark1='" + DateTime.Now.Year + "' and Remark=0";
DataTable dtOld = DBUnity.AdapterToTab(sqlstr11);
DataTable dtNew = new DataTable();
dt.Columns.Add("aaaa");
for (int i = 0; i < dt.Rows.Count; i++)
{
RM_keshiyewufei rk = new RM_keshiyewufei();
rk.Depname = dt.Rows[i]["部门名"].ToString();
rk.SQJE = ParseUtil.ToDecimal(dt.Rows[i]["上年余额"].ToString(), 0);
rk.BQJE = ParseUtil.ToDecimal(dt.Rows[i]["本期金额"].ToString(), 0);
rk.ZJJE = ParseUtil.ToDecimal(dt.Rows[i]["追加金额"].ToString(), 0);
rk.LSJL = rk.ZJJE.ToString() + "#" + DateTime.Now.ToString("yyyy-MM-dd") + "@";
string aa = rk.LSJL.ToString();
decimal zj = 0;
for (int j = 0; j < aa.Trim('@').Split('@').Length; j++)
{
zj += ParseUtil.ToDecimal(aa.Trim('@').Split('@')[j].Split('#')[0], 0);
}
rk.KYJE = zj + rk.BQJE + rk.SQJE;
rk.Remark = 1;
rk.Remark1 = DateTime.Now.Year.ToString();
RM_keshiyewufeiManager.AddRM_keshiyewufei(rk);
}
Expense.Methods.MessageBox.Show(this, "导入成功");
RpDataBind();
}
//for (int i = 1; i < dt.Rows.Count; i++)
//{
// RM_keshiyewufei rk = new RM_keshiyewufei();
// rk.Depname = dt.Rows[i]["F1"].ToString();
// rk.SQJE = ParseUtil.ToDecimal(dt.Rows[i]["F2"].ToString(), 0);
// rk.BQJE = ParseUtil.ToDecimal(dt.Rows[i]["F3"].ToString(), 0);
// rk.ZJJE = ParseUtil.ToDecimal(dt.Rows[i]["F4"].ToString(), 0);
// rk.Remark = 0;
// rk.Remark1 = DateTime.Now.Year.ToString();
// RM_keshiyewufeiManager.AddRM_keshiyewufei(rk);
//}
//MessageBox.Show(this, "导入成功");
//RpDataBind();
}
catch
{
}
}
public void RaisePostBackEvent(string eventArgument)
{
DataTable dt = new DataTable();
dt = ImportExcelFile(hidpath.Value);
switch (eventArgument)
{
case "是":
decimal je = 0;
for (int i = 0; i < dt.Rows.Count; i++)
{
je += ParseUtil.ToDecimal(dt.Rows[i]["追加金额"].ToString(), 0);
}
string sqlstr11 = "select * from RM_keshiyewufei where Remark1='" + DateTime.Now.Year + "' and Remark=1";
DataTable dtOld = DBUnity.AdapterToTab(sqlstr11);
DataTable dtNew = new DataTable();
dt.Columns.Add("aaaa");
for (int i = 0; i < dt.Rows.Count; i++)
{
string depname = dt.Rows[i]["部门名"].ToString();
for (int j = 0; j < dtOld.Rows.Count; j++)
{
if (dtOld.Rows[j]["Depname"].ToString() == depname)
{
string zj = dtOld.Rows[j]["LSJL"].ToString();
if (je > 0)
{
zj += dt.Rows[i]["追加金额"].ToString() + "#" + DateTime.Now.ToString("yyyy-MM-dd") + "@";
}
dt.Rows[i]["aaaa"] = zj;
}
else
{
RM_keshiyewufei rk = new RM_keshiyewufei();
rk.Depname = dt.Rows[i]["部门名"].ToString();
rk.SQJE = ParseUtil.ToDecimal(dt.Rows[i]["本期金额"].ToString(), 0);
rk.BQJE = ParseUtil.ToDecimal(dt.Rows[i]["上年余额"].ToString(), 0);
rk.ZJJE = ParseUtil.ToDecimal(dt.Rows[i]["追加金额"].ToString(), 0);
string aa = dt.Rows[i]["aaaa"].ToString();
rk.LSJL = rk.ZJJE + "#" + DateTime.Now.ToString("yyyy-MM-dd") + "@";
rk.KYJE = rk.ZJJE + rk.BQJE + rk.SQJE;
rk.Remark = 1;
rk.Remark1 = DateTime.Now.Year.ToString();
RM_keshiyewufeiManager.AddRM_keshiyewufei(rk);
}
}
}
string strsql = "delete from RM_keshiyewufei where Remark1='" + DateTime.Now.Year + "' and Remark=1";
DBUnity.ExecuteNonQuery(CommandType.Text, strsql, null);
for (int i = 0; i < dt.Rows.Count; i++)
{
RM_keshiyewufei rk = new RM_keshiyewufei();
rk.Depname = dt.Rows[i]["部门名"].ToString();
rk.SQJE = ParseUtil.ToDecimal(dt.Rows[i]["上年余额"].ToString(), 0);
rk.BQJE = ParseUtil.ToDecimal(dt.Rows[i]["本期金额"].ToString(), 0);
rk.ZJJE = ParseUtil.ToDecimal(dt.Rows[i]["追加金额"].ToString(), 0);
rk.LSJL = dt.Rows[i]["aaaa"].ToString();
string aa = dt.Rows[i]["aaaa"].ToString();
decimal zj = 0;
for (int j = 0; j < aa.Trim('@').Split('@').Length; j++)
{
zj += ParseUtil.ToDecimal(aa.Trim('@').Split('@')[j].Split('#')[0], 0);
}
rk.KYJE = zj + rk.BQJE + rk.SQJE;
rk.Remark = 1;
rk.Remark1 = DateTime.Now.Year.ToString();
RM_keshiyewufeiManager.AddRM_keshiyewufei(rk);
}
Expense.Methods.MessageBox.Show(this, "导入成功");
RpDataBind();
break;
case "否":
string sqlstr = "select * from RM_keshiyewufei where Remark1='" + DateTime.Now.Year + "' and Remark=1";
DataTable dtUpd = DBUnity.AdapterToTab(sqlstr);
dt.Columns.Add("newje");
for (int i = 0; i < dt.Rows.Count; i++)
{
string depname = dt.Rows[i]["部门名"].ToString();
for (int j = 0; j < dtUpd.Rows.Count; j++)
{
if (dtUpd.Rows[j]["Depname"].ToString() == depname)
{
string zj = dtUpd.Rows[j]["LSJL"].ToString().TrimEnd('@');
if (zj.Contains("@"))
{
zj = zj.Substring(0, zj.LastIndexOf('@'));
zj += dt.Rows[i]["追加金额"].ToString() + "#" + DateTime.Now.ToString("yyyy-MM-dd") + "@";
}
else
{
zj = dt.Rows[i]["追加金额"].ToString() + "#" + DateTime.Now.ToString("yyyy-MM-dd") + "@";
}
dt.Rows[i]["newje"] = zj;
}
}
}
string strsqlde = "delete from RM_keshiyewufei where Remark1='" + DateTime.Now.Year + "' and Remark=1";
DBUnity.ExecuteNonQuery(CommandType.Text, strsqlde, null);
for (int i = 0; i < dt.Rows.Count; i++)
{
RM_keshiyewufei rk = new RM_keshiyewufei();
rk.Depname = dt.Rows[i]["部门名"].ToString();
rk.SQJE = ParseUtil.ToDecimal(dt.Rows[i]["上年余额"].ToString(), 0);
rk.BQJE = ParseUtil.ToDecimal(dt.Rows[i]["本期金额"].ToString(), 0);
rk.ZJJE = ParseUtil.ToDecimal(dt.Rows[i]["追加金额"].ToString(), 0);
rk.LSJL = dt.Rows[i]["newje"].ToString();
string aa = dt.Rows[i]["newje"].ToString();
decimal zj = 0;
for (int j = 0; j < aa.Trim('@').Split('@').Length; j++)
{
zj += ParseUtil.ToDecimal(aa.Trim('@').Split('@')[j].Split('#')[0], 0);
}
rk.KYJE = zj + rk.BQJE + rk.SQJE;
rk.Remark = 0;
rk.Remark1 = DateTime.Now.Year.ToString();
RM_keshiyewufeiManager.AddRM_keshiyewufei(rk);
}
MessageBox.Show(this, "导入成功");
RpDataBind();
break;
}
}
public static RM_keshiyewufei GetRM_keshiyewufeiByKSID(int kSID)
{
string sql = "SELECT * FROM RM_keshiyewufei WHERE KSID = @KSID";
try
{
SqlParameter para = new SqlParameter("@KSID", kSID);
DataTable dt = DBUnity.AdapterToTab(sql, para);
if (dt.Rows.Count > 0)
{
RM_keshiyewufei rM_keshiyewufei = new RM_keshiyewufei();
rM_keshiyewufei.KSID = dt.Rows[0]["KSID"] == DBNull.Value ? 0 : (int)dt.Rows[0]["KSID"];
rM_keshiyewufei.Depname = dt.Rows[0]["Depname"] == DBNull.Value ? "" : (string)dt.Rows[0]["Depname"];
rM_keshiyewufei.BQJE = dt.Rows[0]["BQJE"] == DBNull.Value ? 0 : (decimal)dt.Rows[0]["BQJE"];
rM_keshiyewufei.SQJE = dt.Rows[0]["SQJE"] == DBNull.Value ? 0 : (decimal)dt.Rows[0]["SQJE"];
rM_keshiyewufei.ZJJE = dt.Rows[0]["ZJJE"] == DBNull.Value ? 0 : (decimal)dt.Rows[0]["ZJJE"];
rM_keshiyewufei.KYJE = dt.Rows[0]["KYJE"] == DBNull.Value ? 0 : (decimal)dt.Rows[0]["KYJE"];
rM_keshiyewufei.Remark = dt.Rows[0]["Remark"] == DBNull.Value ? 0 : (decimal)dt.Rows[0]["Remark"];
rM_keshiyewufei.Remark1 = dt.Rows[0]["Remark1"] == DBNull.Value ? "" : (string)dt.Rows[0]["Remark1"];
rM_keshiyewufei.LSJL = dt.Rows[0]["LSJL"] == DBNull.Value ? "" : (string)dt.Rows[0]["LSJL"];
return rM_keshiyewufei;
}
else
{
return null;
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
throw e;
}
}
public static bool DeleteRM_keshiyewufei(RM_keshiyewufei rM_keshiyewufei)
{
return DeleteRM_keshiyewufeiByKSID(rM_keshiyewufei.KSID);
}
public static bool ModifyRM_keshiyewufei(RM_keshiyewufei rM_keshiyewufei)
{
string sql =
"UPDATE RM_keshiyewufei " +
"SET " +
"Depname = @Depname, " +
"BQJE = @BQJE, " +
"SQJE = @SQJE, " +
"ZJJE = @ZJJE, " +
"KYJE = @KYJE, " +
"Remark = @Remark, " +
"Remark1 = @Remark1, " +
"LSJL = @LSJL " +
"WHERE KSID = @KSID";
try
{
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@KSID", rM_keshiyewufei.KSID),
new SqlParameter("@Depname", rM_keshiyewufei.Depname),
new SqlParameter("@BQJE", rM_keshiyewufei.BQJE),
new SqlParameter("@SQJE", rM_keshiyewufei.SQJE),
new SqlParameter("@ZJJE", rM_keshiyewufei.ZJJE),
new SqlParameter("@KYJE", rM_keshiyewufei.KYJE),
new SqlParameter("@Remark", rM_keshiyewufei.Remark),
new SqlParameter("@Remark1", rM_keshiyewufei.Remark1),
new SqlParameter("@LSJL", rM_keshiyewufei.LSJL)
};
int t = DBUnity.ExecuteNonQuery(CommandType.Text, sql, para);
if (t > 0)
{
return true;
}
else
{
return false;
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
throw e;
}
}