/// <summary>
/// 删除一条数据
/// </summary>
public bool Delete(int UserID, int ID)
{
Model.SOURCEVIEW original = GetModel(UserID, ID);
StringBuilder strSql = new StringBuilder();
strSql.Append("delete from SOURCEVIEW ");
strSql.Append(" where ID=@ID");
SqlParameter[] parameters =
{
new SqlParameter("@ID", SqlDbType.Int, 4)
};
parameters[0].Value = ID;
int rows = DbHelperSQL.ExecuteSql(UserID, strSql.ToString(), parameters);
if (rows > 0)
{
Model.AUDITLOG auditobj = original.GetAuditLogObject(null);
auditobj.UserID = UserID;
auditobj.CreateDate = DateTime.Now;
auditobj.MessageType = Model.AUDITLOG.Severity.Audit;
auditobj.ModuleName = "DAL.SOURCEVIEW.Delete";
auditobj.Message = String.Format(AppNum.AuditMessage.SourceViewDeleteSuccess, ID);
AUDITLOG.Add(auditobj);
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 删除一条数据
/// </summary>
public bool Delete(int UserID, int ID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("delete from [USER] ");
strSql.Append(" where ID=@ID");
SqlParameter[] parameters =
{
new SqlParameter("@ID", SqlDbType.Int, 4)
};
parameters[0].Value = ID;
int rows = DbHelperSQL.ExecuteSql(UserID, strSql.ToString(), parameters);
if (rows > 0)
{
Model.AUDITLOG auditobj = new Model.AUDITLOG();
auditobj.UserID = UserID;
auditobj.CreateDate = DateTime.Now;
auditobj.MessageType = Model.AUDITLOG.Severity.Audit;
auditobj.ModuleName = "DAL.USER.Delete";
auditobj.Message = String.Format(AppNum.AuditMessage.UserDeleteSuccess, ID);
AUDITLOG.Add(auditobj);
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 删除一条数据
/// </summary>
public bool Delete(int UserID, int REPORT, string NAME)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("delete from QUERYPARAMS ");
strSql.Append(" where REPORT=@REPORT and NAME=@NAME ");
SqlParameter[] parameters =
{
new SqlParameter("@REPORT", SqlDbType.Int),
new SqlParameter("@NAME", SqlDbType.NVarChar, 50)
};
parameters[0].Value = REPORT;
parameters[1].Value = NAME;
int rows = DbHelperSQL.ExecuteSql(UserID, strSql.ToString(), parameters);
if (rows > 0)
{
Model.AUDITLOG auditobj = new Model.AUDITLOG();
auditobj.UserID = UserID;
auditobj.CreateDate = DateTime.Now;
auditobj.MessageType = Model.AUDITLOG.Severity.Audit;
auditobj.ModuleName = "DAL.QUERYPARAMS.Delete";
auditobj.Message = String.Format(AppNum.AuditMessage.QueryParamsDeleteSuccess, REPORT, NAME);
AUDITLOG.Add(auditobj);
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// Create AUDITLOG object and set MessageDetail according to object difference
/// </summary>
/// <param name="newvalue">Model holding new values</param>
/// <param name="oldvalue">Model holding original values, can be null if it's insert action</param>
/// <returns></returns>
internal static Model.AUDITLOG GetAuditLogObject(this Model.SOURCEVIEW newvalue, Model.SOURCEVIEW oldvalue)
{
Model.AUDITLOG result = new Model.AUDITLOG();
PropertyInfo[] pinfo = typeof(Model.SOURCEVIEW).GetProperties();
StringBuilder sb = new StringBuilder();
if (oldvalue == null)
{
// new record
foreach (PropertyInfo p in pinfo)
{
switch (p.Name)
{
case "SOURCETYPE":
case "FORMATTYPE":
{
// skip these properties
}
break;
default:
{
sb.AppendFormat("{0} = {1}\r\n", p.Name, p.GetValue(newvalue, null));
}
break;
}
}
}
else
{
// old record
foreach (PropertyInfo p in pinfo)
{
switch (p.Name)
{
case "SOURCETYPE":
case "FORMATTYPE":
{
// skip these properties
}
break;
default:
{
if (p.GetValue(newvalue, null) != p.GetValue(oldvalue, null))
{
sb.AppendFormat("{0} = {1} -> {2}\r\n", p.Name, p.GetValue(oldvalue, null), p.GetValue(newvalue, null));
}
}
break;
}
}
}
result.MessageDetail = sb.ToString();
return(result);
}
/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(int UserID, CUSTOMRP.Model.SOURCEVIEW model)
{
Model.SOURCEVIEW original = GetModel(UserID, model.ID);
StringBuilder strSql = new StringBuilder();
strSql.Append("update SOURCEVIEW set ");
strSql.Append("SOURCEVIEWNAME=@SOURCEVIEWNAME,");
strSql.Append("DATABASEID=@DATABASEID,");
strSql.Append("SOURCETYPE=@SOURCETYPE,");
strSql.Append("TBLVIEWNAME=@TBLVIEWNAME,");
strSql.Append("AUDODATE=@AUDODATE,");
strSql.Append("VIEWLEVEL=@VIEWLEVEL,");
strSql.Append("[DESC]=@DESC,");
strSql.Append("FORMATTYPE=@FORMATTYPE");
strSql.Append(" where ID=@ID");
SqlParameter[] parameters =
{
new SqlParameter("@SOURCEVIEWNAME", SqlDbType.NVarChar, 50),
new SqlParameter("@DATABASEID", SqlDbType.Int, 4),
new SqlParameter("@SOURCETYPE", SqlDbType.Int, 4),
new SqlParameter("@TBLVIEWNAME", SqlDbType.NVarChar, 128),
new SqlParameter("@AUDODATE", SqlDbType.DateTime),
new SqlParameter("@VIEWLEVEL", SqlDbType.Decimal, 9),
new SqlParameter("@DESC", SqlDbType.NVarChar, 50),
new SqlParameter("@FORMATTYPE", SqlDbType.Int, 4),
new SqlParameter("@ID", SqlDbType.Int, 4)
};
parameters[0].Value = model.SOURCEVIEWNAME;
parameters[1].Value = model.DATABASEID;
parameters[2].Value = model.SOURCETYPE;
parameters[3].Value = model.TBLVIEWNAME;
parameters[4].Value = model.AUDODATE;
parameters[5].Value = model.VIEWLEVEL;
parameters[6].Value = model.DESC;
parameters[7].Value = model.FORMATTYPE;
parameters[8].Value = model.ID;
int rows = DbHelperSQL.ExecuteSql(UserID, strSql.ToString(), parameters);
if (rows > 0)
{
Model.AUDITLOG auditobj = model.GetAuditLogObject(original);
auditobj.UserID = UserID;
auditobj.CreateDate = DateTime.Now;
auditobj.MessageType = Model.AUDITLOG.Severity.Audit;
auditobj.ModuleName = "DAL.SOURCEVIEW.Update";
auditobj.Message = String.Format(AppNum.AuditMessage.SourceViewUpdateSuccess, model.ID);
AUDITLOG.Add(auditobj);
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(CUSTOMRP.Model.DATABASE model)
{
if (String.IsNullOrEmpty(model.HASHKEY))
{
model.HASHKEY = "com";
}
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into [DATABASE](");
strSql.Append("APPLICATIONID,NAME,[DESC],STATUS,LASTMODIFYDATE,LASTMODIFYUSER,AUDOTIME,HASHKEY)");
strSql.Append(" values (");
strSql.Append("@APPLICATIONID,@NAME,@DESC,@STATUS,@LASTMODIFYDATE,@LASTMODIFYUSER,@AUDOTIME,@HASHKEY)");
strSql.Append(";select @@IDENTITY");
SqlParameter[] parameters =
{
new SqlParameter("@APPLICATIONID", SqlDbType.Int, 4),
new SqlParameter("@NAME", SqlDbType.NVarChar, 50),
new SqlParameter("@DESC", SqlDbType.NVarChar, 2000),
new SqlParameter("@STATUS", SqlDbType.Int, 4),
new SqlParameter("@LASTMODIFYDATE", SqlDbType.DateTime),
new SqlParameter("@LASTMODIFYUSER", SqlDbType.Int, 4),
new SqlParameter("@AUDOTIME", SqlDbType.DateTime),
new SqlParameter("@HASHKEY", SqlDbType.NVarChar, 50)
};
parameters[0].Value = model.APPLICATIONID;
parameters[1].Value = model.NAME;
parameters[2].Value = model.DESC;
parameters[3].Value = model.STATUS;
parameters[4].Value = model.LASTMODIFYDATE;
parameters[5].Value = model.LASTMODIFYUSER;
parameters[6].Value = model.AUDOTIME;
parameters[7].Value = model.HASHKEY;
object obj = DbHelperSQL.GetSingle(model.LASTMODIFYUSER, strSql.ToString(), parameters);
if (obj == null)
{
return(0);
}
else
{
model.ID = Convert.ToInt32(obj);
Model.AUDITLOG auditobj = model.GetAuditLogObject(null);
auditobj.UserID = model.LASTMODIFYUSER;
auditobj.CreateDate = DateTime.Now;
auditobj.MessageType = Model.AUDITLOG.Severity.Audit;
auditobj.ModuleName = "DAL.DATABASE.Add";
auditobj.Message = String.Format(AppNum.AuditMessage.DatabaseInsertSuccess, model.ID);
AUDITLOG.Add(auditobj);
return(model.ID);
}
}
/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(CUSTOMRP.Model.APPLICATION model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update APPLICATION set ");
strSql.Append("NAME=@NAME,");
strSql.Append("DESC=@DESC,");
strSql.Append("CONFIG_VIEW1=@CONFIG_VIEW1,");
strSql.Append("CONFIG_VIEW2=@CONFIG_VIEW2,");
strSql.Append("CONFIG_VIEW3=@CONFIG_VIEW3,");
strSql.Append("LASTMODIFYDATE=@LASTMODIFYDATE,");
strSql.Append("LASTMODIFYUSER=@LASTMODIFYUSER,");
strSql.Append("AUDODATE=@AUDODATE");
strSql.Append(" where ID=@ID");
SqlParameter[] parameters =
{
new SqlParameter("@NAME", SqlDbType.NVarChar, 50),
new SqlParameter("@DESC", SqlDbType.NVarChar, -1),
new SqlParameter("@CONFIG_VIEW1", SqlDbType.NVarChar, -1),
new SqlParameter("@CONFIG_VIEW2", SqlDbType.NVarChar, -1),
new SqlParameter("@CONFIG_VIEW3", SqlDbType.NVarChar, -1),
new SqlParameter("@LASTMODIFYDATE", SqlDbType.DateTime),
new SqlParameter("@LASTMODIFYUSER", SqlDbType.Int, 4),
new SqlParameter("@AUDODATE", SqlDbType.DateTime),
new SqlParameter("@ID", SqlDbType.Int, 4)
};
parameters[0].Value = model.NAME;
parameters[1].Value = model.DESC;
parameters[2].Value = model.CONFIG_VIEW1;
parameters[3].Value = model.CONFIG_VIEW2;
parameters[4].Value = model.CONFIG_VIEW3;
parameters[5].Value = model.LASTMODIFYDATE;
parameters[6].Value = model.LASTMODIFYUSER;
parameters[7].Value = model.AUDODATE;
parameters[8].Value = model.ID;
int rows = DbHelperSQL.ExecuteSql(model.LASTMODIFYUSER, strSql.ToString(), parameters);
if (rows > 0)
{
Model.AUDITLOG auditobj = model.GetAuditLogObject(null);
auditobj.UserID = model.LASTMODIFYUSER;
auditobj.CreateDate = DateTime.Now;
auditobj.MessageType = Model.AUDITLOG.Severity.Audit;
auditobj.ModuleName = "DAL.APPLICATION.Update";
auditobj.Message = String.Format(AppNum.AuditMessage.ApplicationUpdateSuccess, model.ID);
AUDITLOG.Add(auditobj);
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(int UserID, CUSTOMRP.Model.SOURCEVIEW model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into SOURCEVIEW(");
strSql.Append("SOURCEVIEWNAME,DATABASEID,SOURCETYPE,TBLVIEWNAME,AUDODATE,VIEWLEVEL,[DESC],FORMATTYPE)");
strSql.Append(" values (");
strSql.Append("@SOURCEVIEWNAME,@DATABASEID,@SOURCETYPE,@TBLVIEWNAME,@AUDODATE,@VIEWLEVEL,@DESC,@FORMATTYPE)");
strSql.Append(";select @@IDENTITY");
SqlParameter[] parameters =
{
new SqlParameter("@SOURCEVIEWNAME", SqlDbType.NVarChar, 50),
new SqlParameter("@DATABASEID", SqlDbType.Int, 4),
new SqlParameter("@SOURCETYPE", SqlDbType.Int, 4),
new SqlParameter("@TBLVIEWNAME", SqlDbType.NVarChar, 128),
new SqlParameter("@AUDODATE", SqlDbType.DateTime),
new SqlParameter("@VIEWLEVEL", SqlDbType.Decimal, 9),
new SqlParameter("@DESC", SqlDbType.NVarChar, 50),
new SqlParameter("@FORMATTYPE", SqlDbType.Int, 4)
};
parameters[0].Value = model.SOURCEVIEWNAME;
parameters[1].Value = model.DATABASEID;
parameters[2].Value = model.SOURCETYPE;
parameters[3].Value = model.TBLVIEWNAME;
parameters[4].Value = model.AUDODATE;
parameters[5].Value = model.VIEWLEVEL;
parameters[6].Value = model.DESC;
parameters[7].Value = model.FORMATTYPE;
object obj = DbHelperSQL.GetSingle(UserID, strSql.ToString(), parameters);
if (obj == null)
{
return(0);
}
else
{
model.ID = Convert.ToInt32(obj);
Model.AUDITLOG auditobj = model.GetAuditLogObject(null);
auditobj.UserID = UserID;
auditobj.CreateDate = DateTime.Now;
auditobj.MessageType = Model.AUDITLOG.Severity.Audit;
auditobj.ModuleName = "DAL.SOURCEVIEW.Add";
auditobj.Message = String.Format(AppNum.AuditMessage.SourceViewInsertSuccess, model.ID);
AUDITLOG.Add(auditobj);
return(model.ID);
}
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(CUSTOMRP.Model.APPLICATION model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into APPLICATION(");
strSql.Append("NAME,[DESC],CONFIG_VIEW1,CONFIG_VIEW2,CONFIG_VIEW3,LASTMODIFYDATE,LASTMODIFYUSER,AUDODATE)");
strSql.Append(" values (");
strSql.Append("@NAME,@DESC,@CONFIG_VIEW1,@CONFIG_VIEW2,@CONFIG_VIEW3,@LASTMODIFYDATE,@LASTMODIFYUSER,@AUDODATE)");
strSql.Append(";select @@IDENTITY");
SqlParameter[] parameters =
{
new SqlParameter("@NAME", SqlDbType.NVarChar, 50),
new SqlParameter("@DESC", SqlDbType.NVarChar, -1),
new SqlParameter("@CONFIG_VIEW1", SqlDbType.NVarChar, -1),
new SqlParameter("@CONFIG_VIEW2", SqlDbType.NVarChar, -1),
new SqlParameter("@CONFIG_VIEW3", SqlDbType.NVarChar, -1),
new SqlParameter("@LASTMODIFYDATE", SqlDbType.DateTime),
new SqlParameter("@LASTMODIFYUSER", SqlDbType.Int, 4),
new SqlParameter("@AUDODATE", SqlDbType.DateTime)
};
parameters[0].Value = model.NAME;
parameters[1].Value = model.DESC;
parameters[2].Value = model.CONFIG_VIEW1;
parameters[3].Value = model.CONFIG_VIEW2;
parameters[4].Value = model.CONFIG_VIEW3;
parameters[5].Value = model.LASTMODIFYDATE;
parameters[6].Value = model.LASTMODIFYUSER;
parameters[7].Value = model.AUDODATE;
object obj = DbHelperSQL.GetSingle(model.LASTMODIFYUSER, strSql.ToString(), parameters);
if (obj == null)
{
return(0);
}
else
{
model.ID = Convert.ToInt32(obj);
Model.AUDITLOG auditobj = model.GetAuditLogObject(null);
auditobj.UserID = model.LASTMODIFYUSER;
auditobj.CreateDate = DateTime.Now;
auditobj.MessageType = Model.AUDITLOG.Severity.Audit;
auditobj.ModuleName = "DAL.APPLICATION.Add";
auditobj.Message = String.Format(AppNum.AuditMessage.ApplicationInsertSuccess, model.ID);
AUDITLOG.Add(auditobj);
return(model.ID);
}
}
/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(int UserID, CUSTOMRP.Model.USERGROUP model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update USERGROUP set ");
strSql.Append("DATABASEID=@DATABASEID,");
strSql.Append("NAME=@NAME,");
strSql.Append("DESCRIPTION=@DESCRIPTION,");
strSql.Append("AUDODATE=@AUDODATE,");
strSql.Append("EXTEND=@EXTEND");
strSql.Append(" where ID=@ID");
SqlParameter[] parameters =
{
new SqlParameter("@DATABASEID", SqlDbType.Int, 4),
new SqlParameter("@NAME", SqlDbType.NVarChar, 50),
new SqlParameter("@DESCRIPTION", SqlDbType.NVarChar, -1),
new SqlParameter("@AUDODATE", SqlDbType.DateTime),
new SqlParameter("@EXTEND", SqlDbType.NVarChar, -1),
new SqlParameter("@ID", SqlDbType.Int, 4)
};
parameters[0].Value = model.DATABASEID;
parameters[1].Value = model.NAME;
parameters[2].Value = model.DESCRIPTION;
parameters[3].Value = model.AUDODATE;
parameters[4].Value = model.EXTEND;
parameters[5].Value = model.ID;
int rows = DbHelperSQL.ExecuteSql(UserID, strSql.ToString(), parameters);
if (rows > 0)
{
Model.AUDITLOG auditobj = model.GetAuditLogObject(null);
auditobj.UserID = UserID;
auditobj.CreateDate = DateTime.Now;
auditobj.MessageType = Model.AUDITLOG.Severity.Audit;
auditobj.ModuleName = "DAL.USERGROUP.Update";
auditobj.Message = String.Format(AppNum.AuditMessage.UserGroupUpdateSuccess, model.ID);
AUDITLOG.Add(auditobj);
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(int UserID, CUSTOMRP.Model.SensitivityLevel model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into SensitivityLevel(");
strSql.Append("DATABASEID,NAME,SLEVEL,DESCRIPTION,AUDOTIME)");
strSql.Append(" values (");
strSql.Append("@DATABASEID,@NAME,@SLEVEL,@DESCRIPTION,@AUDOTIME)");
strSql.Append(";select @@IDENTITY");
SqlParameter[] parameters =
{
new SqlParameter("@DATABASEID", SqlDbType.Int, 4),
new SqlParameter("@NAME", SqlDbType.NVarChar, 50),
new SqlParameter("@SLEVEL", SqlDbType.Decimal, 9),
new SqlParameter("@DESCRIPTION", SqlDbType.NVarChar, -1),
new SqlParameter("@AUDOTIME", SqlDbType.DateTime)
};
parameters[0].Value = model.DATABASEID;
parameters[1].Value = model.NAME;
parameters[2].Value = model.SLEVEL;
parameters[3].Value = model.DESCRIPTION;
parameters[4].Value = model.AUDOTIME;
object obj = DbHelperSQL.GetSingle(UserID, strSql.ToString(), parameters);
if (obj == null)
{
return(0);
}
else
{
model.ID = Convert.ToInt32(obj);
Model.AUDITLOG auditobj = model.GetAuditLogObject(null);
auditobj.UserID = UserID;
auditobj.CreateDate = DateTime.Now;
auditobj.MessageType = Model.AUDITLOG.Severity.Audit;
auditobj.ModuleName = "DAL.SENSITIVITYLEVEL.Add";
auditobj.Message = String.Format(AppNum.AuditMessage.SensitivityLevelInsertSuccess, model.ID);
AUDITLOG.Add(auditobj);
return(Convert.ToInt32(obj));
}
}
/// <summary>
/// Create AUDITLOG object and set MessageDetail according to object difference.
/// </summary>
/// <param name="newvalue">Model holding new values</param>
/// <param name="oldvalue">Model holding original values, can be null if it's insert action</param>
/// <returns></returns>
public static Model.AUDITLOG GetAuditLogObject(this object newvalue, object oldvalue)
{
Model.AUDITLOG result = new Model.AUDITLOG();
PropertyInfo[] pinfo = newvalue.GetType().GetProperties();
StringBuilder sb = new StringBuilder();
if (oldvalue == null)
{
// new record
foreach (PropertyInfo p in pinfo)
{
switch (p.Name)
{
default:
{
sb.AppendFormat("{0} = {1}\r\n", p.Name, p.GetValue(newvalue, null));
}
break;
}
}
}
else
{
// old record
foreach (PropertyInfo p in pinfo)
{
switch (p.Name)
{
default:
{
if (p.GetValue(newvalue, null) != p.GetValue(oldvalue, null))
{
sb.AppendFormat("{0} = {1} -> {2}\r\n", p.Name, p.GetValue(oldvalue, null), p.GetValue(newvalue, null));
}
}
break;
}
}
}
result.MessageDetail = sb.ToString();
return(result);
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(int UserID, CUSTOMRP.Model.USERGROUP model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into USERGROUP(");
strSql.Append("DATABASEID,NAME,DESCRIPTION,AUDODATE,EXTEND)");
strSql.Append(" values (");
strSql.Append("@DATABASEID,@NAME,@DESCRIPTION,@AUDODATE,@EXTEND)");
strSql.Append(";select @@IDENTITY");
SqlParameter[] parameters =
{
new SqlParameter("@DATABASEID", SqlDbType.Int, 4),
new SqlParameter("@NAME", SqlDbType.NVarChar, 50),
new SqlParameter("@DESCRIPTION", SqlDbType.NVarChar, -1),
new SqlParameter("@AUDODATE", SqlDbType.DateTime),
new SqlParameter("@EXTEND", SqlDbType.NVarChar, -1)
};
parameters[0].Value = model.DATABASEID;
parameters[1].Value = model.NAME;
parameters[2].Value = model.DESCRIPTION;
parameters[3].Value = model.AUDODATE;
parameters[4].Value = model.EXTEND;
object obj = DbHelperSQL.GetSingle(UserID, strSql.ToString(), parameters);
if (obj == null)
{
return(0);
}
else
{
model.ID = Convert.ToInt32(obj);
Model.AUDITLOG auditobj = model.GetAuditLogObject(null);
auditobj.UserID = UserID;
auditobj.CreateDate = DateTime.Now;
auditobj.MessageType = Model.AUDITLOG.Severity.Audit;
auditobj.ModuleName = "DAL.USERGROUP.Add";
auditobj.Message = String.Format(AppNum.AuditMessage.UserGroupInsertSuccess, model.ID);
AUDITLOG.Add(auditobj);
return(model.ID);
}
}
/// <summary>
/// 删除一条数据
/// </summary>
public bool Delete(int UserID, int ID)
{
Model.REPORT original = GetModel(UserID, ID);
#region Clear columns from ReportColumn table first
REPORTCOLUMN dalRC = new REPORTCOLUMN();
List <Model.REPORTCOLUMN> rclist = dalRC.GetModelListForReport(UserID, ID);
foreach (Model.REPORTCOLUMN rc in rclist)
{
dalRC.Delete(UserID, rc.ID);
}
#endregion
StringBuilder strSql = new StringBuilder();
strSql.Append("delete from REPORT ");
strSql.Append(" where ID=@ID");
SqlParameter[] parameters =
{
new SqlParameter("@ID", SqlDbType.Int, 4)
};
parameters[0].Value = ID;
int rows = DbHelperSQL.ExecuteSql(UserID, strSql.ToString(), parameters);
if (rows > 0)
{
Model.AUDITLOG auditobj = original.GetAuditLogObject(null);
auditobj.UserID = UserID;
auditobj.CreateDate = DateTime.Now;
auditobj.MessageType = Model.AUDITLOG.Severity.Audit;
auditobj.ModuleName = "DAL.REPORT.Delete";
auditobj.Message = String.Format(AppNum.AuditMessage.ReportDeleteSuccess, ID);
AUDITLOG.Add(auditobj);
return(true);
}
else
{
return(false);
}
}
public bool DeleteForSourceView(int UserID, int SVID)
{
Model.SOURCEVIEWCOLUMN[] original = GetModelsForSourceView(UserID, SVID, true).ToArray();
int rows = DbHelperSQL.ExecuteSql(UserID, "DELETE FROM SOURCEVIEWCOLUMN WHERE SVID = @SVID",
new SqlParameter("@SVID", SVID)
);
if (rows > 0)
{
Model.AUDITLOG auditobj = original.GetAuditLogObject(null);
auditobj.UserID = UserID;
auditobj.CreateDate = DateTime.Now;
auditobj.MessageType = Model.AUDITLOG.Severity.Audit;
auditobj.ModuleName = "DAL.SOURCEVIEWCOLUMN.DeleteForSourceView.Delete";
auditobj.Message = String.Format(AppNum.AuditMessage.SourceViewColumnDeleteSuccess, SVID);
AUDITLOG.Add(auditobj);
}
return(rows > 0);
}
/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(int UserID, CUSTOMRP.Model.QUERYPARAMS model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update QUERYPARAMS set ");
strSql.Append("REPORT=@REPORT,");
strSql.Append("NAME=@NAME,");
strSql.Append("VALUE=@VALUE");
strSql.Append(" where REPORT=@REPORT and NAME=@NAME");
SqlParameter[] parameters =
{
new SqlParameter("@REPORT", SqlDbType.Int),
new SqlParameter("@NAME", SqlDbType.NVarChar,50),
new SqlParameter("@VALUE", SqlDbType.NVarChar, 100)
};
parameters[0].Value = model.REPORT;
parameters[1].Value = model.NAME;
parameters[2].Value = model.VALUE;
int rows = DbHelperSQL.ExecuteSql(UserID, strSql.ToString(), parameters);
if (rows > 0)
{
Model.AUDITLOG auditobj = model.GetAuditLogObject(null);
auditobj.UserID = UserID;
auditobj.CreateDate = DateTime.Now;
auditobj.MessageType = Model.AUDITLOG.Severity.Audit;
auditobj.ModuleName = "DAL.QUERYPARAMS.Update";
auditobj.Message = String.Format(AppNum.AuditMessage.QueryParamsUpdateSuccess, model.REPORT, model.NAME);
AUDITLOG.Add(auditobj);
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(CUSTOMRP.Model.USER model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into [USER](");
strSql.Append("UID,GID,DATABASEID,PASSWORD,VIEWLEVEL,REPORTGROUPLIST,USERGROUPLEVEL,SETUPUSER,REPORTRIGHT,AUTODATE,EMAIL,USERGROUP,NAME,SENSITIVITYLEVEL)");
strSql.Append(" values (");
strSql.Append("@UID,@GID,@DATABASEID,@PASSWORD,@VIEWLEVEL,@REPORTGROUPLIST,@USERGROUPLEVEL,@SETUPUSER,@REPORTRIGHT,@AUTODATE,@EMAIL,@USERGROUP,@NAME,@SENSITIVITYLEVEL)");
strSql.Append(";select @@IDENTITY");
SqlParameter[] parameters =
{
new SqlParameter("@UID", SqlDbType.NVarChar, 200),
new SqlParameter("@GID", SqlDbType.Int, 4),
new SqlParameter("@DATABASEID", SqlDbType.Int, 4),
new SqlParameter("@PASSWORD", SqlDbType.NVarChar, 50),
new SqlParameter("@VIEWLEVEL", SqlDbType.NVarChar, 2000),
new SqlParameter("@REPORTGROUPLIST", SqlDbType.NVarChar, 2000),
new SqlParameter("@USERGROUPLEVEL", SqlDbType.NVarChar, 2000),
new SqlParameter("@SETUPUSER", SqlDbType.Int, 4),
new SqlParameter("@REPORTRIGHT", SqlDbType.Int, 4),
new SqlParameter("@AUTODATE", SqlDbType.DateTime),
new SqlParameter("@EMAIL", SqlDbType.NVarChar, 2000),
new SqlParameter("@USERGROUP", SqlDbType.NVarChar, 100),
new SqlParameter("@NAME", SqlDbType.NVarChar, 50),
new SqlParameter("@SENSITIVITYLEVEL", SqlDbType.Decimal, 9)
};
parameters[0].Value = model.UID;
parameters[1].Value = model.GID;
parameters[2].Value = model.DATABASEID;
parameters[3].Value = model.PASSWORD;
parameters[4].Value = model.VIEWLEVEL;
parameters[5].Value = model.REPORTGROUPLIST;
parameters[6].Value = model.USERGROUPLEVEL;
parameters[7].Value = model.SETUPUSER;
parameters[8].Value = model.REPORTRIGHT;
parameters[9].Value = model.AUTODATE;
parameters[10].Value = model.EMAIL;
parameters[11].Value = model.USERGROUP;
parameters[12].Value = model.NAME;
parameters[13].Value = model.SENSITIVITYLEVEL;
object obj = DbHelperSQL.GetSingle(model.SETUPUSER, strSql.ToString(), parameters);
if (obj == null)
{
return(0);
}
else
{
model.ID = Convert.ToInt32(obj);
Model.AUDITLOG auditobj = model.GetAuditLogObject(null);
auditobj.UserID = model.SETUPUSER;
auditobj.CreateDate = DateTime.Now;
auditobj.MessageType = Model.AUDITLOG.Severity.Audit;
auditobj.ModuleName = "DAL.USER.Add";
auditobj.Message = String.Format(AppNum.AuditMessage.UserInsertSuccess, model.ID);
AUDITLOG.Add(auditobj);
return(model.ID);
}
}
/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(CUSTOMRP.Model.USER model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update [USER] set ");
strSql.Append("DATABASEID=@DATABASEID,");
strSql.Append("PASSWORD=@PASSWORD,");
strSql.Append("VIEWLEVEL=@VIEWLEVEL,");
strSql.Append("REPORTGROUPLIST=@REPORTGROUPLIST,");
strSql.Append("USERGROUPLEVEL=@USERGROUPLEVEL,");
strSql.Append("SETUPUSER=@SETUPUSER,");
strSql.Append("REPORTRIGHT=@REPORTRIGHT,");
strSql.Append("AUTODATE=@AUTODATE,");
strSql.Append("EMAIL=@EMAIL,");
strSql.Append("USERGROUP=@USERGROUP,");
strSql.Append("NAME=@NAME,");
strSql.Append("SENSITIVITYLEVEL=@SENSITIVITYLEVEL,");
strSql.Append("GID=@GID");
strSql.Append(" where ID=@ID");
SqlParameter[] parameters =
{
new SqlParameter("@DATABASEID", SqlDbType.Int, 4),
new SqlParameter("@PASSWORD", SqlDbType.NVarChar, 50),
new SqlParameter("@VIEWLEVEL", SqlDbType.NVarChar, 2000),
new SqlParameter("@REPORTGROUPLIST", SqlDbType.NVarChar, 2000),
new SqlParameter("@USERGROUPLEVEL", SqlDbType.NVarChar, 2000),
new SqlParameter("@SETUPUSER", SqlDbType.Int, 4),
new SqlParameter("@REPORTRIGHT", SqlDbType.Int, 4),
new SqlParameter("@AUTODATE", SqlDbType.DateTime),
new SqlParameter("@EMAIL", SqlDbType.NVarChar, 2000),
new SqlParameter("@USERGROUP", SqlDbType.NVarChar, 100),
new SqlParameter("@NAME", SqlDbType.NVarChar, 50),
new SqlParameter("@SENSITIVITYLEVEL", SqlDbType.Decimal, 9),
new SqlParameter("@ID", SqlDbType.Int, 4),
new SqlParameter("@UID", SqlDbType.NVarChar, 200),
new SqlParameter("@GID", SqlDbType.Int, 4)
};
parameters[0].Value = model.DATABASEID;
parameters[1].Value = model.PASSWORD;
parameters[2].Value = model.VIEWLEVEL;
parameters[3].Value = model.REPORTGROUPLIST;
parameters[4].Value = model.USERGROUPLEVEL;
parameters[5].Value = model.SETUPUSER;
parameters[6].Value = model.REPORTRIGHT;
parameters[7].Value = model.AUTODATE;
parameters[8].Value = model.EMAIL;
parameters[9].Value = model.USERGROUP;
parameters[10].Value = model.NAME;
parameters[11].Value = model.SENSITIVITYLEVEL;
parameters[12].Value = model.ID;
parameters[13].Value = model.UID;
parameters[14].Value = model.GID;
int rows = DbHelperSQL.ExecuteSql(model.SETUPUSER, strSql.ToString(), parameters);
if (rows > 0)
{
Model.AUDITLOG auditobj = model.GetAuditLogObject(null);
auditobj.UserID = model.SETUPUSER;
auditobj.CreateDate = DateTime.Now;
auditobj.MessageType = Model.AUDITLOG.Severity.Audit;
auditobj.ModuleName = "DAL.USER.Update";
auditobj.Message = String.Format(AppNum.AuditMessage.UserUpdateSuccess, model.ID);
AUDITLOG.Add(auditobj);
return(true);
}
else
{
return(false);
}
}
public bool Replace(CUSTOMRP.Model.REPORT model)
{
bool result = false;
bool isNew = false;
CUSTOMRP.Model.REPORT original = null;
if (model.ID == 0)
{
model.ID = Add(model);
result = model.ID != 0;
isNew = true;
}
else
{
original = GetModel(model.ADDUSER, model.ID);
result = Update(model);
}
if (!result)
{
return(false);
} // error occurred
// prepare AuditLog object
Model.AUDITLOG auditobj = model.GetAuditLogObject(original);
auditobj.UserID = model.ADDUSER;
auditobj.CreateDate = DateTime.Now;
#region Report Columns handling
try
{
CUSTOMRP.DAL.REPORTCOLUMN dalRC = new REPORTCOLUMN();
//v1.0.0 - Cheong - 2015/06/30 - delete removed columns
if (!isNew)
{
Model.REPORTCOLUMN[] dbRC = (from oldrc in dalRC.GetModelListForReport(model.ADDUSER, model.ID)
join newrc in model.ReportColumns on new { oldrc.COLUMNNAME, oldrc.COLUMNFUNC, oldrc.COLUMNTYPE } equals new { newrc.COLUMNNAME, newrc.COLUMNFUNC, newrc.COLUMNTYPE } into g
from gjoin in g.DefaultIfEmpty()
where gjoin == null
select oldrc).ToArray();
foreach (Model.REPORTCOLUMN rc in dbRC)
{
dalRC.Delete(model.ADDUSER, rc.ID);
}
}
//v1.0.0 - Cheong - 2016/03/23 - Preserve order on criteria and group columns
int contentSEQ = 1;
int criteriaSEQ = 1;
int sortonSEQ = 1;
int groupSEQ = 1;
foreach (CUSTOMRP.Model.REPORTCOLUMN rc in model.ReportColumns)
{
rc.RPID = model.ID;
switch (rc.COLUMNFUNC)
{
case 1:
{
rc.SEQ = contentSEQ;
contentSEQ++;
}
break;
case 2:
{
rc.SEQ = criteriaSEQ;
criteriaSEQ++;
}
break;
case 3:
{
rc.SEQ = sortonSEQ;
sortonSEQ++;
}
break;
case 6:
{
rc.SEQ = groupSEQ;
groupSEQ++;
}
break;
default:
{
rc.SEQ = -1;
}
break;
}
//if (rc.COLUMNFUNC == 1)
//{
// rc.SEQ = contentSEQ;
// contentSEQ++;
//}
//else if (rc.COLUMNFUNC == 3)
//{
// rc.SEQ = sortonSEQ;
// sortonSEQ++;
//}
//else
//{
// rc.SEQ = -1;
//}
result = dalRC.Replace(model.ADDUSER, rc);
if (!result)
{
return(false);
}
}
}
catch
{
result = false;
}
#endregion Report Columns handling
#region WordFile handling
try
{
if (model.WordFile != null)
{
DAL.WORDFILE dalWF = new WORDFILE();
Model.WORDFILE wf = dalWF.GetModelByReportID(model.ADDUSER, model.ID);
if (wf == null)
{
if (model.WordFile.RPID == 0)
{
model.WordFile.RPID = model.ID;
}
dalWF.AddFile(model.WordFile);
}
else
{
wf.Description = model.WordFile.Description;
wf.WordFileName = model.WordFile.WordFileName;
wf.OrigFileName = model.WordFile.OrigFileName;
wf.ModifyUser = model.WordFile.ModifyUser;
wf.ModifyDate = model.WordFile.ModifyDate;
dalWF.UpdateFile(wf);
}
}
}
catch
{
result = false;
}
#endregion WordFile handling
auditobj.MessageType = Model.AUDITLOG.Severity.Audit;
auditobj.ModuleName = "DAL.REPORT.REPLACE";
auditobj.Message = String.Format(isNew ? AppNum.AuditMessage.ReportInsertSuccess : AppNum.AuditMessage.ReportUpdateSuccess, model.ID);
AUDITLOG.Add(auditobj);
return(result);
}
/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(CUSTOMRP.Model.DATABASE model)
{
if (String.IsNullOrEmpty(model.HASHKEY))
{
model.HASHKEY = "com";
}
StringBuilder strSql = new StringBuilder();
strSql.Append("update [DATABASE] set ");
strSql.Append("APPLICATIONID=@APPLICATIONID,");
strSql.Append("NAME=@NAME,");
strSql.Append("[DESC]=@DESC,");
strSql.Append("STATUS=@STATUS,");
strSql.Append("LASTMODIFYDATE=@LASTMODIFYDATE,");
strSql.Append("LASTMODIFYUSER=@LASTMODIFYUSER,");
strSql.Append("AUDOTIME=@AUDOTIME,");
strSql.Append("HASHKEY=@HASHKEY");
strSql.Append(" where ID=@ID");
SqlParameter[] parameters =
{
new SqlParameter("@APPLICATIONID", SqlDbType.Int, 4),
new SqlParameter("@NAME", SqlDbType.NVarChar, 50),
new SqlParameter("@DESC", SqlDbType.NVarChar, 2000),
new SqlParameter("@STATUS", SqlDbType.Int, 4),
new SqlParameter("@LASTMODIFYDATE", SqlDbType.DateTime),
new SqlParameter("@LASTMODIFYUSER", SqlDbType.Int, 4),
new SqlParameter("@AUDOTIME", SqlDbType.DateTime),
new SqlParameter("@HASHKEY", SqlDbType.NVarChar, 50),
new SqlParameter("@ID", SqlDbType.Int, 4)
};
parameters[0].Value = model.APPLICATIONID;
parameters[1].Value = model.NAME;
parameters[2].Value = model.DESC;
parameters[3].Value = model.STATUS;
parameters[4].Value = model.LASTMODIFYDATE;
parameters[5].Value = model.LASTMODIFYUSER;
parameters[6].Value = model.AUDOTIME;
parameters[7].Value = model.HASHKEY;
parameters[8].Value = model.ID;
int rows = DbHelperSQL.ExecuteSql(model.LASTMODIFYUSER, strSql.ToString(), parameters);
if (rows > 0)
{
Model.AUDITLOG auditobj = model.GetAuditLogObject(null);
auditobj.UserID = model.LASTMODIFYUSER;
auditobj.CreateDate = DateTime.Now;
auditobj.MessageType = Model.AUDITLOG.Severity.Audit;
auditobj.ModuleName = "DAL.DATABASE.Update";
auditobj.Message = String.Format(AppNum.AuditMessage.DatabaseUpdateSuccess, model.ID);
AUDITLOG.Add(auditobj);
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(int UserID, CUSTOMRP.Model.GROUPRIGHT model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update GROUPRIGHT set ");
strSql.Append("COMPANY=@COMPANY,");
strSql.Append("REPORTGROUP=@REPORTGROUP,");
strSql.Append("CATEGARY=@CATEGARY,");
strSql.Append("SECURITY=@SECURITY,");
strSql.Append("QUERY=@QUERY,");
strSql.Append("USERGROUP=@USERGROUP,");
strSql.Append("USERGROUPRIGHT=@USERGROUPRIGHT,");
strSql.Append("USERSETUP=@USERSETUP,");
strSql.Append("AUDODATE=@AUDODATE,");
strSql.Append("EXTEND1=@EXTEND1,");
strSql.Append("EXTEND2=@EXTEND2,");
strSql.Append("EXTEND3=@EXTEND3");
strSql.Append(" where ID=@ID");
SqlParameter[] parameters =
{
new SqlParameter("@COMPANY", SqlDbType.NVarChar, 50),
new SqlParameter("@REPORTGROUP", SqlDbType.NVarChar, 50),
new SqlParameter("@CATEGARY", SqlDbType.NVarChar, 50),
new SqlParameter("@SECURITY", SqlDbType.NVarChar, 50),
new SqlParameter("@QUERY", SqlDbType.NVarChar, 50),
new SqlParameter("@USERGROUP", SqlDbType.NVarChar, 50),
new SqlParameter("@USERGROUPRIGHT", SqlDbType.NVarChar, 50),
new SqlParameter("@USERSETUP", SqlDbType.NVarChar, 50),
new SqlParameter("@AUDODATE", SqlDbType.DateTime),
new SqlParameter("@EXTEND1", SqlDbType.NVarChar, 50),
new SqlParameter("@EXTEND2", SqlDbType.NVarChar, 50),
new SqlParameter("@EXTEND3", SqlDbType.NVarChar, -1),
new SqlParameter("@ID", SqlDbType.Int, 4),
new SqlParameter("@GID", SqlDbType.Int, 4)
};
parameters[0].Value = model.COMPANY;
parameters[1].Value = model.REPORTGROUP;
parameters[2].Value = model.CATEGARY;
parameters[3].Value = model.SECURITY;
parameters[4].Value = model.QUERY;
parameters[5].Value = model.USERGROUP;
parameters[6].Value = model.USERGROUPRIGHT;
parameters[7].Value = model.USERSETUP;
parameters[8].Value = model.AUDODATE;
parameters[9].Value = model.EXTEND1;
parameters[10].Value = model.EXTEND2;
parameters[11].Value = model.EXTEND3;
parameters[12].Value = model.ID;
parameters[13].Value = model.GID;
int rows = DbHelperSQL.ExecuteSql(UserID, strSql.ToString(), parameters);
if (rows > 0)
{
Model.AUDITLOG auditobj = model.GetAuditLogObject(null);
auditobj.UserID = UserID;
auditobj.CreateDate = DateTime.Now;
auditobj.MessageType = Model.AUDITLOG.Severity.Audit;
auditobj.ModuleName = "DAL.GROUPRIGHT.Update";
auditobj.Message = String.Format(AppNum.AuditMessage.GroupRightUpdateSuccess, model.ID);
AUDITLOG.Add(auditobj);
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(int UserID, CUSTOMRP.Model.GROUPRIGHT model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into GROUPRIGHT(");
strSql.Append("GID,COMPANY,REPORTGROUP,CATEGARY,SECURITY,QUERY,USERGROUP,USERGROUPRIGHT,USERSETUP,AUDODATE,EXTEND1,EXTEND2,EXTEND3)");
strSql.Append(" values (");
strSql.Append("@GID,@COMPANY,@REPORTGROUP,@CATEGARY,@SECURITY,@QUERY,@USERGROUP,@USERGROUPRIGHT,@USERSETUP,@AUDODATE,@EXTEND1,@EXTEND2,@EXTEND3)");
strSql.Append(";select @@IDENTITY");
SqlParameter[] parameters =
{
new SqlParameter("@GID", SqlDbType.Int, 4),
new SqlParameter("@COMPANY", SqlDbType.NVarChar, 50),
new SqlParameter("@REPORTGROUP", SqlDbType.NVarChar, 50),
new SqlParameter("@CATEGARY", SqlDbType.NVarChar, 50),
new SqlParameter("@SECURITY", SqlDbType.NVarChar, 50),
new SqlParameter("@QUERY", SqlDbType.NVarChar, 50),
new SqlParameter("@USERGROUP", SqlDbType.NVarChar, 50),
new SqlParameter("@USERGROUPRIGHT", SqlDbType.NVarChar, 50),
new SqlParameter("@USERSETUP", SqlDbType.NVarChar, 50),
new SqlParameter("@AUDODATE", SqlDbType.DateTime),
new SqlParameter("@EXTEND1", SqlDbType.NVarChar, 50),
new SqlParameter("@EXTEND2", SqlDbType.NVarChar, 50),
new SqlParameter("@EXTEND3", SqlDbType.NVarChar, -1)
};
parameters[0].Value = model.GID;
parameters[1].Value = model.COMPANY;
parameters[2].Value = model.REPORTGROUP;
parameters[3].Value = model.CATEGARY;
parameters[4].Value = model.SECURITY;
parameters[5].Value = model.QUERY;
parameters[6].Value = model.USERGROUP;
parameters[7].Value = model.USERGROUPRIGHT;
parameters[8].Value = model.USERSETUP;
parameters[9].Value = model.AUDODATE;
parameters[10].Value = model.EXTEND1;
parameters[11].Value = model.EXTEND2;
parameters[12].Value = model.EXTEND3;
object obj = DbHelperSQL.GetSingle(UserID, strSql.ToString(), parameters);
if (obj == null)
{
return(0);
}
else
{
model.ID = Convert.ToInt32(obj);
Model.AUDITLOG auditobj = model.GetAuditLogObject(null);
auditobj.UserID = UserID;
auditobj.CreateDate = DateTime.Now;
auditobj.MessageType = Model.AUDITLOG.Severity.Audit;
auditobj.ModuleName = "DAL.GROUPRIGHT.Add";
auditobj.Message = String.Format(AppNum.AuditMessage.GroupRightInsertSuccess, model.ID);
AUDITLOG.Add(auditobj);
return(Convert.ToInt32(obj));
}
}
/// <summary>
/// Create AUDITLOG object and set MessageDetail according to object difference
/// </summary>
/// <param name="newvalue">Model holding new values</param>
/// <param name="oldvalue">Model holding original values, can be null if it's insert action</param>
/// <returns></returns>
internal static Model.AUDITLOG GetAuditLogObject(this Model.REPORT newvalue, Model.REPORT oldvalue)
{
Model.AUDITLOG result = new Model.AUDITLOG();
PropertyInfo[] pinfo = typeof(Model.REPORT).GetProperties();
StringBuilder sb = new StringBuilder();
if (oldvalue == null)
{
// new record
foreach (PropertyInfo p in pinfo)
{
switch (p.Name)
{
case "TYPE":
case "EXTENDFIELD":
case "PRINT_ORIENTATION":
case "REPORT_HEADER":
{
// skip these properties
}
break;
case "ReportColumns":
{
PropertyInfo[] rcinfo = typeof(Model.REPORTCOLUMN).GetProperties();
for (int i = 0; i < newvalue.ReportColumns.Count; i++)
{
foreach (PropertyInfo rcp in rcinfo)
{
switch (rcp.Name)
{
case "COLUMNFUNC":
case "COLUMNTYPE":
case "SelectStatement":
{
// skip these properties
}
break;
default:
{
sb.AppendFormat("ReportColumns[{0}].{1} = {2}\r\n", i, rcp.Name, rcp.GetValue(newvalue.ReportColumns[i], null));
}
break;
}
}
}
}
break;
case "WordFile":
{
if (newvalue.WordFile != null)
{
PropertyInfo[] wfinfo = typeof(Model.WORDFILE).GetProperties();
foreach (PropertyInfo wfp in wfinfo)
{
sb.AppendFormat("WordFile.{0} = {1}\r\n", wfp.Name, wfp.GetValue(newvalue.WordFile, null));
}
}
else
{
sb.Append("WordFile = (null)\r\n");
}
}
break;
default:
{
sb.AppendFormat("{0} = {1}\r\n", p.Name, p.GetValue(newvalue, null));
}
break;
}
}
}
else
{
// old record
foreach (PropertyInfo p in pinfo)
{
switch (p.Name)
{
case "TYPE":
case "EXTENDFIELD":
case "PRINT_ORIENTATION":
case "REPORT_HEADER":
{
// skip these properties
}
break;
case "ReportColumns":
{
PropertyInfo[] rcinfo = typeof(Model.REPORTCOLUMN).GetProperties();
#region Check for new and updated columns
Model.REPORTCOLUMN oldrc = null;
for (int i = 0; i < newvalue.ReportColumns.Count; i++)
{
oldrc = oldvalue.ReportColumns.Where(x => (x.COLUMNFUNC == newvalue.ReportColumns[i].COLUMNFUNC) &&
(x.COLUMNTYPE == newvalue.ReportColumns[i].COLUMNTYPE) &&
(x.DisplayName == newvalue.ReportColumns[i].DisplayName)).FirstOrDefault();
if (oldrc == null)
{
// new report column
foreach (PropertyInfo rcp in rcinfo)
{
switch (rcp.Name)
{
case "COLUMNFUNC":
case "COLUMNTYPE":
case "SelectStatement":
{
// skip these properties
}
break;
default:
{
sb.AppendFormat("ReportColumns[{0}].{1} = {2}\r\n", i, rcp.Name, rcp.GetValue(newvalue.ReportColumns[i], null));
}
break;
}
}
}
else
{
// modified column
foreach (PropertyInfo rcp in rcinfo)
{
switch (rcp.Name)
{
case "COLUMNFUNC":
case "COLUMNTYPE":
case "SelectStatement":
{
// skip these properties
}
break;
default:
{
if (Convert.ToString(rcp.GetValue(newvalue.ReportColumns[i], null)) != Convert.ToString(rcp.GetValue(oldrc, null)))
{
sb.AppendFormat("ReportColumns[{0}].{1} = {2} -> {3}\r\n", i, rcp.Name, rcp.GetValue(oldrc, null), rcp.GetValue(newvalue.ReportColumns[i], null));
}
}
break;
}
}
}
}
#endregion Check for new and updated columns
#region Check for delete columns
int deletecounter = 0;
foreach (Model.REPORTCOLUMN item in oldvalue.ReportColumns)
{
if (newvalue.ReportColumns.Where(x => (x.COLUMNFUNC == item.COLUMNFUNC) &&
(x.COLUMNTYPE == item.COLUMNTYPE) &&
(x.DisplayName == item.DisplayName)).Count() == 0)
{
foreach (PropertyInfo rcp in rcinfo)
{
switch (rcp.Name)
{
case "COLUMNFUNC":
case "COLUMNTYPE":
case "SelectStatement":
{
// skip these properties
}
break;
default:
{
sb.AppendFormat("ReportColumns[deleted {0}].{1} = {2}\r\n", deletecounter, rcp.Name, rcp.GetValue(item, null));
}
break;
}
}
deletecounter++;
}
}
#endregion
}
break;
case "WordFile":
{
if (newvalue.WordFile != null)
{
if (oldvalue.WordFile != null)
{
PropertyInfo[] wfinfo = typeof(Model.WORDFILE).GetProperties();
foreach (PropertyInfo wfp in wfinfo)
{
if (Convert.ToString(wfp.GetValue(newvalue.WordFile, null)) != Convert.ToString(wfp.GetValue(oldvalue.WordFile, null)))
{
sb.AppendFormat("WordFile.{0} = {1} -> {2}\r\n", wfp.Name, wfp.GetValue(oldvalue.WordFile, null), wfp.GetValue(newvalue.WordFile, null));
}
}
}
else
{
// new Wordfile entry
PropertyInfo[] wfinfo = typeof(Model.WORDFILE).GetProperties();
foreach (PropertyInfo wfp in wfinfo)
{
sb.AppendFormat("WordFile.{0} = {1}\r\n", wfp.Name, wfp.GetValue(newvalue.WordFile, null));
}
}
}
else
{
if (oldvalue.WordFile != null)
{
sb.Append("WordFile = (removed)\r\n");
}
}
}
break;
default:
{
if (Convert.ToString(p.GetValue(newvalue, null)) != Convert.ToString(p.GetValue(oldvalue, null)))
{
sb.AppendFormat("{0} = {1} -> {2}\r\n", p.Name, p.GetValue(oldvalue, null), p.GetValue(newvalue, null));
}
}
break;
}
}
}
result.MessageDetail = sb.ToString();
return(result);
}
/// <summary>
/// Create AUDITLOG object and set MessageDetail according to object difference.
/// </summary>
/// <param name="newvalue">Model holding new values</param>
/// <param name="oldvalue">Model holding original values, can be null if it's insert action</param>
/// <returns></returns>
/// <remarks>Because this has to be done in BLL, we need to expose this static method.</remarks>
public static Model.AUDITLOG GetAuditLogObject(this Model.SOURCEVIEWCOLUMN[] newvalue, Model.SOURCEVIEWCOLUMN[] oldvalue)
{
Model.AUDITLOG result = new Model.AUDITLOG();
PropertyInfo[] svcinfo = typeof(Model.SOURCEVIEWCOLUMN).GetProperties();
StringBuilder sb = new StringBuilder();
if (oldvalue == null)
{
// new record
for (int i = 0; i < newvalue.Length; i++)
{
foreach (PropertyInfo svcp in svcinfo)
{
switch (svcp.Name)
{
case "COLUMNTYPE":
case "DisplayName":
case "GetText":
{
// skip these properties
}
break;
default:
{
sb.AppendFormat("SOURCEVIEWCOLUMN[{0}].{1} = {2}\r\n", i, svcp.Name, svcp.GetValue(newvalue[i], null));
}
break;
}
}
}
}
else
{
// old record
#region Check for new and updated columns
Model.SOURCEVIEWCOLUMN oldsvc = null;
for (int i = 0; i < newvalue.Length; i++)
{
oldsvc = oldvalue.Where(x => (x.COLUMNTYPE == newvalue[i].COLUMNTYPE) &&
(x.COLUMNNAME == newvalue[i].COLUMNNAME)).FirstOrDefault();
if (oldsvc == null)
{
// new report column
foreach (PropertyInfo svcp in svcinfo)
{
switch (svcp.Name)
{
case "COLUMNTYPE":
case "DisplayName":
case "GetText":
{
// skip these properties
}
break;
default:
{
sb.AppendFormat("SOURCEVIEWCOLUMN[{0}].{1} = {2}\r\n", i, svcp.Name, svcp.GetValue(newvalue[i], null));
}
break;
}
}
}
else
{
// modified column
foreach (PropertyInfo svcp in svcinfo)
{
switch (svcp.Name)
{
case "COLUMNTYPE":
case "DisplayName":
case "GetText":
{
// skip these properties
}
break;
default:
{
if (Convert.ToString(svcp.GetValue(newvalue[i], null)) != Convert.ToString(svcp.GetValue(oldsvc, null)))
{
sb.AppendFormat("SOURCEVIEWCOLUMN[{0}].{1} = {2} -> {3}\r\n", i, svcp.Name, svcp.GetValue(oldsvc, null), svcp.GetValue(newvalue[i], null));
}
}
break;
}
}
}
}
#endregion Check for new and updated columns
#region Check for delete columns
int deletecounter = 0;
foreach (Model.SOURCEVIEWCOLUMN item in oldvalue)
{
if (newvalue.Where(x => (x.COLUMNTYPE == item.COLUMNTYPE) &&
(x.COLUMNNAME == item.COLUMNNAME)).Count() == 0)
{
foreach (PropertyInfo svcp in svcinfo)
{
switch (svcp.Name)
{
case "COLUMNTYPE":
case "DisplayName":
case "GetText":
{
// skip these properties
}
break;
default:
{
sb.AppendFormat("SOURCEVIEWCOLUMN[deleted {0}].{1} = {2}\r\n", deletecounter, svcp.Name, svcp.GetValue(item, null));
}
break;
}
}
deletecounter++;
}
}
#endregion
}
result.MessageDetail = sb.ToString();
return(result);
}
//internal int Add(int UserID, CUSTOMRP.Model.SOURCEVIEWCOLUMN model)
//{
// return dal.Add(UserID, model);
//}
//internal int AddColList(int UserID, int SVID, string[] columns)
//{
// return dal.AddColList(UserID, SVID, columns);
//}
//internal bool Delete(int UserID, int SVCID)
//{
// return dal.Delete(UserID, SVCID);
//}
//public bool DeleteForSourceView(int SVID)
//{
// return dal.DeleteForSourceView(SVID);
//}
public void UpdateModelList(int UserID, CUSTOMRP.Model.SOURCEVIEWCOLUMN[] modelList)
{
if ((modelList == null) || (modelList.Length == 0))
{
return;
}
CUSTOMRP.Model.SOURCEVIEWCOLUMN[] currentlist = null;
CUSTOMRP.Model.SOURCEVIEWCOLUMN currentitem = null;
CUSTOMRP.Model.SOURCEVIEWCOLUMN[] original = GetModelsForSourceView(UserID, modelList[0].SVID).ToArray();
// prepare AuditLog object
Model.AUDITLOG auditobj = modelList.GetAuditLogObject(original);
auditobj.UserID = UserID;
auditobj.CreateDate = DateTime.Now;
try
{
currentlist = GetModelsForSourceView(UserID, modelList[0].SVID).ToArray();
}
catch
{
currentlist = new CUSTOMRP.Model.SOURCEVIEWCOLUMN[0];
}
#region Delete non-existent columns
foreach (CUSTOMRP.Model.SOURCEVIEWCOLUMN item in currentlist)
{
if (modelList.Where(x => x.COLUMNNAME == item.COLUMNNAME).Count() == 0)
{
dal.Delete(UserID, item.ID);
}
}
foreach (CUSTOMRP.Model.SOURCEVIEWCOLUMN item in modelList)
{
currentitem = currentlist.Where(x => x.COLUMNNAME == item.COLUMNNAME).FirstOrDefault();
if (currentitem == null)
{
dal.Add(UserID, item);
}
else
{
currentitem.DISPLAYNAME = item.DISPLAYNAME;
currentitem.HIDDEN = item.HIDDEN;
currentitem.DATA_TYPE = item.DATA_TYPE;
dal.Update(UserID, currentitem);
}
}
#endregion
auditobj.MessageType = Model.AUDITLOG.Severity.Audit;
auditobj.ModuleName = "BLL.SOURCEVIEWCOLUMN.UpdateModelList";
auditobj.Message = String.Format(original.Length == 0 ? DAL.AppNum.AuditMessage.SourceViewColumnInsertSuccess : DAL.AppNum.AuditMessage.SourceViewColumnUpdateSuccess, modelList[0].SVID);
DAL.AUDITLOG.Add(auditobj);
}
//v1.7.0 Ben 2017.08.21 - Above will no update Hidden as GetModelsForSourceView will not get hidden
public void UpdateModelList(int UserID, CUSTOMRP.Model.SOURCEVIEWCOLUMN[] modelList, bool UpdateHidden)
{
if ((modelList == null) || (modelList.Length == 0))
{
return;
}
CUSTOMRP.Model.SOURCEVIEWCOLUMN[] currentlist = null;
CUSTOMRP.Model.SOURCEVIEWCOLUMN currentitem = null;
CUSTOMRP.Model.SOURCEVIEWCOLUMN[] original = GetModelsForSourceView(UserID, modelList[0].SVID, UpdateHidden).ToArray();
// prepare AuditLog object
Model.AUDITLOG auditobj = modelList.GetAuditLogObject(original);
auditobj.UserID = UserID;
auditobj.CreateDate = DateTime.Now;
currentlist = original;
#region Delete non-existent columns
foreach (CUSTOMRP.Model.SOURCEVIEWCOLUMN item in currentlist)
{
//if (modelList.Where(x => x.COLUMNNAME == item.COLUMNNAME && x.ID != -1).Count() == 0)
if (modelList.Where(x => x.COLUMNNAME.ToUpper() == item.COLUMNNAME.ToUpper() && x.ID != -1).Count() == 0)
{
dal.Delete(UserID, item.ID);
//v1.8.2 Ben 2018.02.22 - Same as below but even deleted record pass to Below seems not update/insert
var models = modelList.Where(x => x.COLUMNNAME.ToUpper() == item.COLUMNNAME.ToUpper() && x.ID != -1);
if (models.Any())
{
models.First().ID = -1;
}
item.ID = 0;
}
}
#endregion
#region Delete existing more than one columns
foreach (CUSTOMRP.Model.SOURCEVIEWCOLUMN item in currentlist)
{
//v1.8.2 Ben 2018.02.26 - Prevent duplicate even just difeerent case
//if (modelList.Where(x => x.COLUMNNAME == item.COLUMNNAME && x.ID != -1).Count() > 1)
if (modelList.Where(x => x.COLUMNNAME.ToUpper() == item.COLUMNNAME.ToUpper() && x.ID != -1).Count() > 1)
{
dal.Delete(UserID, item.ID);
//modelList.Where(x => x.COLUMNNAME == item.COLUMNNAME && x.ID != -1).First().ID = -1;
var models = modelList.Where(x => x.COLUMNNAME.ToUpper() == item.COLUMNNAME.ToUpper() && x.ID != -1);
if (models.Any())
{
models.First().ID = -1;
}
item.ID = 0;
}
}
modelList = modelList.Where(x => x.ID != -1).ToArray();
#endregion
foreach (CUSTOMRP.Model.SOURCEVIEWCOLUMN item in modelList)
{
//currentitem = currentlist.Where(x => x.COLUMNNAME == item.COLUMNNAME).FirstOrDefault();
//v1.8.2 Ben 2018.02.26 - Prevent duplicate even just difeerent case
//currentitem = currentlist.Where(x => x.COLUMNNAME == item.COLUMNNAME).OrderByDescending(x => x.ID).FirstOrDefault();
currentitem = currentlist.Where(x => x.COLUMNNAME.ToUpper() == item.COLUMNNAME.ToUpper()).OrderByDescending(x => x.ID).FirstOrDefault();
if (currentitem == null || currentitem.ID == 0)
{
dal.Add(UserID, item);
}
else
{
//v1.8.2 Ben 2018.02.26 - Use actual case
currentitem.COLUMNNAME = item.COLUMNNAME;
currentitem.DISPLAYNAME = item.DISPLAYNAME;
currentitem.HIDDEN = item.HIDDEN;
dal.Update(UserID, currentitem);
}
}
auditobj.MessageType = Model.AUDITLOG.Severity.Audit;
auditobj.ModuleName = "BLL.SOURCEVIEWCOLUMN.UpdateModelList";
auditobj.Message = String.Format(original.Length == 0 ? DAL.AppNum.AuditMessage.SourceViewColumnInsertSuccess : DAL.AppNum.AuditMessage.SourceViewColumnUpdateSuccess, modelList[0].SVID);
DAL.AUDITLOG.Add(auditobj);
}
