/// <summary>
/// 添加
/// </summary>
/// <param name="entity">实体</param>
/// <returns></returns>
public int Add(System_User entity)
{
try
{
if (dr == null)
{
dr = new CallDatabase();
}
dr.InitiCommand("insert into Sys_User([LoginName],[Pwd],[UserName],[Remarks])values(@LoginName,@Pwd,@UserName,@Remarks);select @@identity");
dr.AddParameters("LoginName", DbType.String, 30, entity.LoginName);
dr.AddParameters("Pwd", DbType.String, 20, entity.Pwd);
dr.AddParameters("UserName", DbType.String, 8, entity.UserName);
dr.AddParameters("Remarks", DbType.String, 200, entity.Remarks);
int value = Convert.ToInt32(dr.ExecuteScalar());
entity.Id = value;
dr.Close();
return(0);
}
catch (Exception ex)
{
using (ILog_Error log = new Log_Error_Generate())
{
log.Title = ex.Message;
log.Content = ex.StackTrace;
log.Generatelog();
}
return(-1);
}
}
/// <summary>
/// 添加
/// </summary>
/// <param name="entity">实体</param>
/// <returns></returns>
public int Add(ParameterValue entity)
{
try
{
entity.Status = "1";
if (dr == null)
{
dr = new CallDatabase();
}
dr.InitiCommand("insert into Sys_ParameterValue([P_Id],[Value],[No],[Memo])values(@P_Id,@Value,@No,@Memo);select @@identity");
dr.AddParameters("P_Id", DbType.Int32, entity.P_Id);
dr.AddParameters("Value", DbType.String, 60, entity.Value);
dr.AddParameters("No", DbType.Int32, entity.No);
dr.AddParameters("Memo", DbType.String, 300, entity.Memo);
int value = Convert.ToInt32(dr.ExecuteScalar());
entity.Id = value;
dr.Close();
return(0);
}
catch (Exception ex)
{
using (ILog_Error log = new Log_Error_Generate())
{
log.Title = ex.Message;
log.Content = ex.StackTrace;
log.Generatelog();
}
return(-1);
}
}
public Paging()
{
dr = new CallDatabase();
PageSize = 50; //默认每页显示50条记录
PageIndex = 1; //默认当前页为第一页
WhereString = ""; //默认是没有条件
}
/// <summary>
/// 禁用全部参数名称数据
/// </summary>
/// <returns>返回数据集</returns>
public DataSet Get_Disabled_Data()
{
if (dr == null)
{
dr = new CallDatabase();
}
dr.InitiCommand("select [Id],[ConfigName],[Memo],[No] from [Sys_ParameterName] where [Status]='0' order by [No] ");
return(dr.DataList());
}
/// <summary>
/// 删除
/// </summary>
/// <param name="id">主键</param>
/// <returns></returns>
public int Delete(int id)
{
if (dr == null)
{
dr = new CallDatabase();
}
dr.InitiCommand("delete from [Expend] where [Id]=@Id ");
dr.AddParameters("Id", DbType.Int32, id);
return(dr.ExecuteNonQuery());
}
/// <summary>
/// 禁用全部参数名称数据
/// </summary>
/// <param name="p_id">Sys_ParameterName的主键</param>
/// <returns>返回数据集</returns>
public DataSet Get_Disabled_Data(int p_id)
{
if (dr == null)
{
dr = new CallDatabase();
}
dr.InitiCommand("select [Id],[Value],[Memo],[No] from [Sys_ParameterValue] where [P_Id]=@P_Id and [Status]='0' order by [No] ");
dr.AddParameters("P_Id", DbType.Int32, p_id);
return(dr.DataList());
}
/// <summary>
/// 根据主键查询数据
/// </summary>
/// <param name="id">主键</param>
/// <returns>返回数据集</returns>
public DataSet Get_Data(int id)
{
if (dr == null)
{
dr = new CallDatabase();
}
dr.InitiCommand("select Id,ExpendDate,ExpendType,ItemName,Amount,Reason,Remarks from [Expend] where [Id]=@Id ");
dr.AddParameters("Id", DbType.Int32, id);
return(dr.DataList());
}
/// <summary>
/// 查询数据集
/// </summary>
/// <returns></returns>
public DataSet Get_Data()
{
if (dr == null)
{
dr = new CallDatabase();
}
dr.InitiCommand("select Id,LoginName,Pwd,UserName,Remarks,IsAdmin from [Sys_User]");
DataSet data = dr.DataList();
dr.Close();
return(data);
}
/// <summary>
/// 删除
/// </summary>
/// <param name="id">主键</param>
/// <returns></returns>
public int Delete(int id)
{
if (dr == null)
{
dr = new CallDatabase();
}
dr.InitiCommand("delete from Sys_User where [Id]=@Id ");
dr.AddParameters("Id", DbType.Int32, id);
int value = dr.ExecuteNonQuery();
dr.Close();
return(value);
}
/// <summary>
/// 返回数据集
/// </summary>
/// <param name="p_Id">Sys_ParameterName的主键</param>
/// <returns></returns>
public DataSet Get_Data(int p_Id)
{
if (dr == null)
{
dr = new CallDatabase();
}
dr.InitiCommand("select Id,Value,Memo,No,Status from Sys_ParameterValue where [P_Id]=@P_Id order by [No]");
dr.AddParameters("P_Id", DbType.Int32, p_Id);
DataSet data = dr.DataList();
dr.Close();
return(data);
}
/// <summary>
/// 根据主键查询数据
/// </summary>
/// <param name="id">主键</param>
/// <returns></returns>
public DataSet Get_SingleData(int id)
{
if (dr == null)
{
dr = new CallDatabase();
}
dr.InitiCommand("select * from [Sys_ParameterValue] where [Id]=@Id");
dr.AddParameters("Id", DbType.Int32, id);
DataSet data = dr.DataList();
dr.Close();
return(data);
}
/// <summary>
/// 返回数据集
/// </summary>
/// <param name="configName">参数名称</param>
/// <returns></returns>
public DataSet Get_Data(string configName)
{
if (dr == null)
{
dr = new CallDatabase();
}
dr.InitiCommand("select * from Sys_ParameterValue v where exists(select 1 from Sys_ParameterName n where v.P_Id=n.Id and n.ConfigName=@ConfigName) order by [No]");
dr.AddParameters("ConfigName", DbType.String, 50, configName);
DataSet data = dr.DataList();
dr.Close();
return(data);
}
/// <summary>
/// 更新密码
/// </summary>
/// <param name="pwd">新密码</param>
/// <param name="id">主键</param>
/// <returns></returns>
public int Update_Pwd(string pwd, string id)
{
if (dr == null)
{
dr = new CallDatabase();
}
dr.InitiCommand("Update Sys_User set [Pwd]=@Pwd where [Id]=@Id ");
dr.AddParameters("Pwd", DbType.String, 20, pwd);
dr.AddParameters("Id", DbType.Int32, id);
int value = dr.ExecuteNonQuery();
dr.Close();
return(value);
}
/// <summary>
/// 根据用户名来获取用户信息
/// </summary>
/// <param name="loginName">用户名</param>
/// <returns></returns>
public DataSet GetLoginInfo(string loginName)
{
if (dr == null)
{
dr = new CallDatabase();
}
dr.InitiCommand();
dr.CommandText = "select * from Sys_User where [LoginName]=@LoginName";
dr.AddParameters("LoginName", DbType.String, 30, loginName);
var data = dr.DataList();
dr.Close();
return(data);
}
/// <summary>
/// 更新状态
/// </summary>
/// <param name="status">状态(1为启用;0为禁用)</param>
/// <param name="id">主键</param>
/// <returns></returns>
public int UpdateStatus(string status, int id)
{
if (dr == null)
{
dr = new CallDatabase();
}
dr.InitiCommand("Update Sys_ParameterValue set [Status]=@Status where [Id]=@Id");
dr.AddParameters("Status", DbType.String, 10, status);
dr.AddParameters("Id", DbType.Int32, id);
int value = dr.ExecuteNonQuery();
dr.Close();
return(value);
}
/// <summary>
/// 根据日期获取数据
/// </summary>
/// <param name="date">日期</param>
/// <param name="loginName">登录用户</param>
/// <returns></returns>
public DataSet GetData(DateTime date, string loginName)
{
ICallDatabase dr = new CallDatabase();
dr.InitiCommand();
dr.CommandText = "select Login_Name,Result,ResultsDescribed,Creation_Date from [Log_Login] where DateDiff(day,Creation_Date,@Creation_Date)=0 And [Login_Name] like @Login_Name order by [Creation_Date] desc";
dr.AddParameters("Creation_Date", DbType.String, 20, date.ToString());
dr.AddParameters("Login_Name", DbType.String, 50, "%" + loginName + "%");
DataSet data = dr.DataList();
dr.Close();
dr.Dispose();
return(data);
}
/// <summary>
/// 获取最大的序号
/// </summary>
/// <returns>返回最大的序号</returns>
public int GetNo()
{
if (dr == null)
{
dr = new CallDatabase();
}
dr.InitiCommand("select max([No]) from [Sys_ParameterName]");
object obj = dr.ExecuteScalar();
if (obj.ToString() != "")
{
return(Convert.ToInt32(obj));
}
return(0);
}
/// <summary>
/// 根据主键来获取数据集
/// </summary>
/// <param name="id">主键</param>
/// <returns></returns>
public DataSet GetData(int id, out DataTable log_Contents_Data)
{
log_Contents_Data = new DataTable();
ICallDatabase dr = new CallDatabase();
dr.InitiCommand();
dr.CommandText = "select Login_Name,ModuleName,OperationType,Result,ResultsDescribed,Creation_Date from [Log_Operating] where Id=@Id ";
dr.AddParameters("Id", DbType.Int32, id);
DataSet data = dr.DataList();
dr.CommandText = "select Contents from [Log_Operating_Contents] where L_Id=@Id order by [No]";
log_Contents_Data = dr.DataList().Tables[0];
dr.Close();
dr.Dispose();
return(data);
}
/// <summary>
/// 更新
/// </summary>
/// <param name="entity">实体</param>
/// <returns></returns>
public int Update(ParameterName entity)
{
if (dr == null)
{
dr = new CallDatabase();
}
dr.InitiCommand("Update Sys_ParameterName set [ConfigName]=@ConfigName,[No]=@No,[Memo]=@Memo where [Id]=@Id ");
dr.AddParameters("ConfigName", DbType.String, 50, entity.ConfigName);
dr.AddParameters("No", DbType.Int32, entity.No);
dr.AddParameters("Memo", DbType.String, 250, entity.Memo);
dr.AddParameters("Id", DbType.Int32, entity.Id);
int value = dr.ExecuteNonQuery();
dr.Close();
return(value);
}
/// <summary>
/// 获取最大的序号
/// </summary>
/// <param name="p_id">Sys_ParameterName的主键</param>
/// <returns>返回最大的序号</returns>
public int GetNo(int p_id)
{
if (dr == null)
{
dr = new CallDatabase();
}
dr.InitiCommand("select max([No]) from [Sys_ParameterValue] where [P_Id]=@P_Id ");
dr.AddParameters("P_Id", DbType.Int32, p_id);
object obj = dr.ExecuteScalar();
if (obj.ToString() != "")
{
return(Convert.ToInt32(obj));
}
return(0);
}
/// <summary>
/// 更新
/// </summary>
/// <param name="entity">实体</param>
/// <returns></returns>
public int Update(ParameterValue entity)
{
if (dr == null)
{
dr = new CallDatabase();
}
dr.InitiCommand("Update Sys_ParameterValue set [Value]=@Value,[No]=@No,[Memo]=@Memo where [Id]=@Id");
dr.AddParameters("Value", DbType.String, 60, entity.Value);
dr.AddParameters("No", DbType.Int32, entity.No);
dr.AddParameters("Memo", DbType.String, 300, entity.Memo);
dr.AddParameters("Id", DbType.Int32, entity.Id);
object value = dr.ExecuteNonQuery();
dr.Close();
return(Convert.ToInt32(value));
}
/// <summary>
/// 更新
/// </summary>
/// <param name="entity">实体</param>
/// <returns></returns>
public int Update(System_User entity)
{
if (dr == null)
{
dr = new CallDatabase();
}
dr.InitiCommand("Update Sys_User set [LoginName]=@LoginName,[Pwd]=@Pwd,[UserName]=@UserName,[Remarks]=@Remarks where [Id]=@Id ");
dr.AddParameters("LoginName", DbType.String, 30, entity.LoginName);
dr.AddParameters("Pwd", DbType.String, 20, entity.Pwd);
dr.AddParameters("UserName", DbType.String, 8, entity.UserName);
dr.AddParameters("Remarks", DbType.String, 200, entity.Remarks);
dr.AddParameters("Id", DbType.Int32, entity.Id);
int value = dr.ExecuteNonQuery();
dr.Close();
return(value);
}
/// <summary>
/// 更新
/// </summary>
/// <param name="entity">实体</param>
/// <returns></returns>
public int Update(Expend entity)
{
if (dr == null)
{
dr = new CallDatabase();
}
dr.InitiCommand("Update Expend set [ExpendDate]=@ExpendDate,[Year_Date]=@Year_Date,[Month_Date]=@Month_Date,[Day_Date]=@Day_Date,[ExpendType]=@ExpendType,[ItemName]=@ItemName,[Amount]=@Amount,[Reason]=@Reason,[Remarks]=@Remarks where [Id]=@Id ");
dr.AddParameters("ExpendDate", DbType.DateTime, entity.ExpendDate);
dr.AddParameters("Year_Date", DbType.String, 20, entity.Year_Date);
dr.AddParameters("Month_Date", DbType.String, 20, entity.Month_Date);
dr.AddParameters("Day_Date", DbType.Int32, entity.Day_Date);
dr.AddParameters("ExpendType", DbType.String, 50, entity.ExpendType);
dr.AddParameters("ItemName", DbType.String, 72, entity.ItemName);
dr.AddParameters("Amount", DbType.Decimal, entity.Amount);
dr.AddParameters("Reason", DbType.String, 250, entity.Reason);
dr.AddParameters("Remarks", DbType.String, 500, entity.Remarks);
dr.AddParameters("Id", DbType.Int32, entity.Id);
return(dr.ExecuteNonQuery());
}
/// <summary>
/// 总金额
/// </summary>
/// <param name="startDate">开始日期</param>
/// <param name="endDate">结束日期</param>
/// <param name="expendType">支付类型</param>
/// <param name="itemName">名称</param>
/// <param name="amount_1">金额_1</param>
/// <param name="amount_2">金额_2</param>
/// <returns>总金额</returns>
public string Get_Total_Amount(DateTime startDate, DateTime endDate, string expendType, string itemName, string amount_1, string amount_2)
{
if (dr == null)
{
dr = new CallDatabase();
}
dr.InitiCommand();
StringBuilder sql = new StringBuilder();
sql.Append("select sum(Amount) from [Expend] where ExpendDate between @startDate and @endDate");
dr.AddParameters("startDate", DbType.String, 50, startDate.ToString("yyyy-MM-dd 00:00:00"));
dr.AddParameters("endDate", DbType.String, 50, endDate.ToString("yyyy-MM-dd 23:59:59"));
if (expendType != null && expendType != "支付类型")
{
sql.Append(" and ExpendType=@expendType ");
dr.AddParameters("expendType", DbType.String, 50, expendType);
}
if (itemName != null && itemName.Length > 0)
{
sql.Append(" and ItemName like '%@itemName%' ");
dr.AddParameters("itemName", DbType.String, 50, itemName);
}
if (amount_1 != null && amount_1.Length > 0 && amount_2 != null && amount_2.Length > 0)
{
sql.Append(" and Amount between @amount_1 and @amount_2 ");
dr.AddParameters("amount_1", DbType.String, 50, amount_1);
dr.AddParameters("amount_2", DbType.String, 50, amount_2);
}
dr.CommandText = sql.ToString();
string value = dr.ExecuteScalar().ToString();
if (value.Length > 0)
{
return(value.ToString());
}
return("0");
}
/// <summary>
/// 获取月的数据
/// </summary>
/// <param name="yaers">年份</param>
/// <param name="expendtype">类型</param>
/// <returns></returns>
public DataSet Get_Month_Data(int yaers, string expendtype)
{
if (dr == null)
{
dr = new CallDatabase();
}
string sql = "select Month_Date,SUM(Amount) AS Amount from [Expend] where [Year_Date]=@Years ";
if (expendtype.Length > 0)
{
sql = sql + " and [ExpendType]=@ExpendType ";
}
sql = sql + " GROUP BY Month_Date";
dr.InitiCommand(sql);
dr.AddParameters("Years", DbType.Int32, yaers);
if (expendtype.Length > 0)
{
dr.AddParameters("ExpendType", DbType.String, 50, expendtype);
}
return(dr.DataList());
}
/// <summary>
/// 获取年的数据
/// </summary>
/// <param name="startDate">开始时间</param>
/// <param name="endDate">结束时间</param>
/// <param name="expendtype">类型</param>
/// <returns></returns>
public DataSet Get_Yaer_Data(int startDate, int endDate, string expendtype)
{
if (dr == null)
{
dr = new CallDatabase();
}
string sql = "select Year_Date, SUM(Amount) AS Amount from [Expend] where (Year_Date BETWEEN @StartDate AND @EndDate) ";
if (expendtype.Length > 0)
{
sql = sql + " and [ExpendType]=@ExpendType ";
}
sql = sql + " GROUP BY Year_Date";
dr.InitiCommand(sql);
dr.AddParameters("StartDate", DbType.Int32, startDate);
dr.AddParameters("EndDate", DbType.Int32, endDate);
if (expendtype.Length > 0)
{
dr.AddParameters("ExpendType", DbType.String, 50, expendtype);
}
return(dr.DataList());
}
/// <summary>
/// 添加
/// </summary>
/// <param name="entity">实体</param>
/// <returns></returns>
public int Add(Expend entity)
{
if (dr == null)
{
dr = new CallDatabase();
}
int value = -1;
try
{
dr.InitiCommand("insert into Expend([ExpendDate],[Year_Date],[Month_Date],[Day_Date],[ExpendType],[ItemName],[Amount],[Reason],[Remarks])values(@ExpendDate,@Year_Date,@Month_Date,@Day_Date,@ExpendType,@ItemName,@Amount,@Reason,@Remarks);SELECT @@IDENTITY;");
dr.AddParameters("ExpendDate", DbType.DateTime, entity.ExpendDate);
dr.AddParameters("Year_Date", DbType.String, 20, entity.Year_Date);
dr.AddParameters("Month_Date", DbType.String, 20, entity.Month_Date);
dr.AddParameters("Day_Date", DbType.Int32, entity.Day_Date);
dr.AddParameters("ExpendType", DbType.String, 50, entity.ExpendType);
dr.AddParameters("ItemName", DbType.String, 72, entity.ItemName);
dr.AddParameters("Amount", DbType.Decimal, entity.Amount);
dr.AddParameters("Reason", DbType.String, 250, entity.Reason);
dr.AddParameters("Remarks", DbType.String, 500, entity.Remarks);
object id = dr.ExecuteScalar();
entity.Id = Convert.ToInt32(id);
value = 1;
}
catch (Exception ex)
{
using (ILog_Error log = new Log_Error_Generate())
{
log.Title = ex.Message;
log.Content = ex.StackTrace;
log.Generatelog();
}
value = -1;
}
return(value);
}
