/// <summary>
/// Delete 1 event
/// </summary>
/// <param name="AppName"></param>
/// <param name="item"></param>
/// <param name="eventname"></param>
/// <returns></returns>
public bool DeleteEvent(string AppName, string item, string eventname)
{
List <string> lstSql = new List <string>();
string sqlString = string.Format(@"select ID from [" + Config._DBNameFrontEnd + "].dbo.SGC_DocumentIssue where AppName = N'{0}' AND Item = N'{1}' AND Event = N'{2}'", AppName, item, eventname);
DataTable kq = ConnectDb.ExcuteQuery(sqlString);
foreach (DataRow row in kq.Rows)
{
string sqlDeleteDocument = string.Format(@"delete from [" + Config._DBNameFrontEnd + "].dbo.SGC_DocumentDetail where ID = '{0}'", row["ID"]);
string sqlDeleteAtt = string.Format(@"delete from [" + Config._DBNameFrontEnd + "].dbo.SGC_Attachment where DocumentID = '{0}'", row["ID"]);
string sqlDeleteDocIssue = string.Format(@"delete from [" + Config._DBNameFrontEnd + "].dbo.SGC_DocumentIssue where ID = '{0}'", row["ID"]);
lstSql.Add(sqlDeleteDocument);
lstSql.Add(sqlDeleteAtt);
lstSql.Add(sqlDeleteDocIssue);
}
try
{
ConnectDb.ExcuteNonQuery_WithTransaction(lstSql);
}
catch (Exception ex)
{
return(false);
}
return(true);
}
/// <summary>
/// Rename Event
/// </summary>
/// <param name="oldEvent"></param>
/// <param name="newEvent"></param>
/// <param name="appName"></param>
/// <param name="Item"></param>
public void UpdateEventName(string oldEvent, string newEvent, string appName, string Item)
{
// update 1 bảng
string sql_SGCDoc = string.Format(@"Update [" + Config._DBNameFrontEnd + "].dbo.SGC_DocumentIssue set Event = N'{0}' where AppName = N'{2}' AND Item = N'{3}' AND Event = N'{1}' ", newEvent, oldEvent, appName, Item);
List <string> lst = new List <string>();
lst.Add(sql_SGCDoc);
ConnectDb.ExcuteNonQuery_WithTransaction(lst);
}
/// <summary>
/// Update item
/// </summary>
/// <param name="oldItem"></param>
/// <param name="newItem"></param>
/// <param name="appName"></param>
public void UpdateItemName(string oldItem, string newItem, string appName)
{
// update 2 bảng
string sql_AppItem = string.Format(@"Update [" + Config._DBNameFrontEnd + "].dbo.SGC_ApplicationItem set Item = N'{0}' where ApplicationName = N'{2}' AND Item = N'{1}'", newItem, oldItem, appName);
string sql_SGCDoc = string.Format(@"Update [" + Config._DBNameFrontEnd + "].dbo.SGC_DocumentIssue set Item = N'{0}' where AppName = N'{2}' AND Item = N'{1}'", newItem, oldItem, appName);
List <string> lst = new List <string>();
lst.Add(sql_AppItem);
lst.Add(sql_SGCDoc);
ConnectDb.ExcuteNonQuery_WithTransaction(lst);
}
bool DeleteinDB(string AttName, string DocumentID)
{
string sql = string.Format(@"select Title from [" + Config._DBNameFrontEnd + "].dbo.SGC_DocumentDetail where ID = '{0}'", DocumentID);
object a = ConnectDb.ExcuteScalar(sql);
if (a == null || a is DBNull)
{
return(false);
}
List <string> lstString = new List <string>();
List <string> lstSql = new List <string>();
string[] lst_db = a.ToString().Split(';');
foreach (string s in lst_db)
{
if (s.Length > 0)
{
int index = s.IndexOf('-') + 1;
int length = s.Length - index;
if (s.Substring(index, length) == AttName)
{
string id = s.Substring(0, index - 1);
string sqlDelete = string.Format(@"delete from [" + Config._DBNameFrontEnd + "].dbo.SGC_Attachment where ID = '{0}'", id);
lstSql.Add(sqlDelete);
}
else
{
lstString.Add(s);
}
}
}
string newTitle = MakeTitle(lstString);
string sqlUpdate = string.Format(@"update [" + Config._DBNameFrontEnd + "].dbo.SGC_DocumentDetail set Title = N'{0}' where ID='{1}'", newTitle, DocumentID);
lstSql.Add(sqlUpdate);
try
{
ConnectDb.ExcuteNonQuery_WithTransaction(lstSql);
}
catch (Exception ex)
{
return(false);
}
return(true);
}
/// <summary>
/// Add new 1 event
/// </summary>
/// <param name="appName"></param>
/// <param name="item"></param>
/// <param name="Event"></param>
/// <param name="idNhanVien"></param>
/// <param name="issueContent"></param>
/// <param name="Version"></param>
/// <param name="reference"></param>
/// <param name="fileNames"></param>
/// <param name="names"></param>
/// <returns></returns>
public string AddNewEvent(string appName, string item, string Event, string idNhanVien, string issueContent, string Version, string reference, List <string> fileNames, List <string> names)
{
string ID = Guid.NewGuid().ToString();
string sqlString_DocumentIssue = string.Format(@"INSERT INTO [" + Config._DBNameFrontEnd + @"].dbo.SGC_DocumentIssue
(
ID,
AppName,
Item,
[Event],
Creator,
DateCreated,
UpdateBy,
DateModify
)
VALUES
(
'{0}',
N'{1}',
N'{2}',
N'{3}',
N'{4}',
@Date,
N'{5}',
{6}
)",
ID, appName, item, Event, idNhanVien, "NULL", "NULL");
SqlParameter param = new SqlParameter("@Date", SqlDbType.DateTime, 8, ParameterDirection.Input, true, 0, 0,
"DateCreated", DataRowVersion.Current, DateTime.Now);
string title = "";
int num = GetMaxNumber();
foreach (string name in names)
{
title += (num + 1) + "-" + name + ";";
num++;
}
issueContent = issueContent.Replace("'", "''");
string sqlString_DocumentDetail = string.Format(@"INSERT INTO [" + Config._DBNameFrontEnd + @"].dbo.SGC_DocumentDetail
(
ID,
[Version],
Reference,
Title,
IssueContent
)
VALUES
(
'{0}',
N'{1}',
N'{2}',
N'{3}',
N'{4}'
)",
ID, Version, reference, title, issueContent);
List <string> lstSql = new List <string>();
lstSql.Add(sqlString_DocumentIssue);
lstSql.Add(sqlString_DocumentDetail);
int nextID = GetMaxNumber();
List <SqlParameter> sqlParams = new List <SqlParameter>();
sqlParams.Add(param);
foreach (string s in fileNames)
{
FileInfo fi = new FileInfo(s);
byte[] b = ConvertFileToByte(fi);
string attName = "@Att" + (nextID + 1).ToString();
string sqlAtt = string.Format(@"INSERT INTO [" + Config._DBNameFrontEnd + @"].dbo.SGC_Attachment
(
ID,
Attachment,
DocumentID
)
VALUES
(
'{0}',
" + attName + @",
'{1}'
)", (nextID + 1), ID);
SqlParameter param_Byte = new SqlParameter(attName, SqlDbType.Binary, 232000, ParameterDirection.Input, true, 0, 0,
"Attachment", DataRowVersion.Current, b);
sqlParams.Add(param_Byte);
nextID++;
lstSql.Add(sqlAtt);
}
if (ConnectDb.ExcuteNonQuery_WithTransaction(lstSql, sqlParams) == 0)
{
return(null);
}
return(ID);
}
/// <summary>
/// Thêm attachment
/// </summary>
/// <param name="s"></param>
/// <param name="DocumentID"></param>
/// <param name="detail"></param>
/// <param name="fileName"></param>
/// <param name="version"></param>
/// <param name="reference"></param>
/// <param name="title_Att"></param>
/// <returns></returns>
public bool AddNewAtt(string s, string DocumentID, string detail,
string fileName, string version, string reference, string title_Att)
{
string sqlString_Title = string.Format(@"select Title from [" + Config._DBNameFrontEnd + "].dbo.SGC_DocumentDetail where ID= '{0}'", DocumentID);
object tt = ConnectDb.ExcuteScalar(sqlString_Title);
string title = "";
if (tt is DBNull || tt == null)
{
title_Att = "";
}
else
{
title_Att = tt.ToString();
}
int nextID = GetMaxNumber();
FileInfo fi = new FileInfo(s);
byte[] b = ConvertFileToByte(fi);
string attName = "@Att" + (nextID + 1).ToString();
string sqlAtt = string.Format(@"INSERT INTO [" + Config._DBNameFrontEnd + @"].dbo.SGC_Attachment
(
ID,
Attachment,
DocumentID
)
VALUES
(
'{0}',
" + attName + @",
'{1}'
)", (nextID + 1), DocumentID);
SqlParameter param_Byte = new SqlParameter(attName, SqlDbType.Binary, b.Length, ParameterDirection.Input, true,
0, 0, "Attachment", DataRowVersion.Current, b);
int num = GetMaxNumber();
title += (num + 1) + "-" + fileName + ";";
detail = detail.Replace("'", "''");
string sqlString = string.Format(@"UPDATE [" + Config._DBNameFrontEnd + @"].dbo.SGC_DocumentDetail
SET IssueContent = N'{0}',
[Version] = N'{2}',
Reference = N'{3}',
Title = N'{4}'
WHERE ID = '{1}'", detail, DocumentID, version, reference, title_Att + title);
List <string> sqlStrings = new List <string>();
sqlStrings.Add(sqlAtt);
sqlStrings.Add(sqlString);
List <SqlParameter> lstParam = new List <SqlParameter>();
lstParam.Add(param_Byte);
try
{
if (ConnectDb.ExcuteNonQuery_WithTransaction(sqlStrings, lstParam) > 0)
{
return(true);
}
}
catch (Exception ex)
{
return(false);
}
return(true);
}