/// <summary>
/// Truy vấn danh sách event
/// </summary>
/// <param name="ApplicationName"></param>
/// <param name="Item"></param>
/// <returns></returns>
public DataTable LoadEventList(string ApplicationName, string Item)
{
string sqlString = string.Format(@" select * from [" + Config._DBNameFrontEnd + "].dbo.SGC_DocumentIssue where AppName = N'{0}' and Item =N'{1}' ORDER BY Event",
ApplicationName, Item);
return(ConnectDb.ExcuteQuery(sqlString));
}
public int insert(byte[] file)
{
string sqlString = string.Format(@"INSERT INTO [" + Config._DBNameFrontEnd + @"].dbo.SGC_Attachment
(
ID,
Attachment,
DocumentID
)
VALUES
(
NEWID(),
@file,
'8981dcc6-7fae-420c-8bb3-31308d43eb7e')
");
SqlParameter param = new SqlParameter("@file", SqlDbType.Binary, 5);
param.Value = file;
SqlParameter[] param_list = new SqlParameter[]
{
param
};
int rowEff = ConnectDb.ExcuteNonQuery(sqlString, param_list);
return(rowEff);
}
/// <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);
}
public DataTable LoadDetailForApp(string AppName)
{
string sqlString = string.Format("select Item from [" + Config._DBNameFrontEnd + "].dbo.SGC_ApplicationItem where ApplicationName = N'{0}' and len(ltrim(Item)) <> 0 order by Item",
AppName);
return(ConnectDb.ExcuteQuery(sqlString));
}
/// <summary>
/// Truy vấn tất cả application
/// </summary>
/// <returns></returns>
public DataTable LoadAllApplication()
{
string sqlString = string.Format(@"select distinct ApplicationName from [" + Config._DBNameFrontEnd + "].dbo.SGC_ApplicationItem order by ApplicationName");
DataTable appRel = ConnectDb.ExcuteQuery(sqlString);
return(appRel);
}
/// <summary>
/// Add new Application với tên item rỗng
/// </summary>
/// <param name="AppName"></param>
/// <param name="Item"></param>
/// <returns></returns>
public int AddNewApp(string AppName, string Item)
{
string sqlString = string.Format(@"INSERT INTO [" + Config._DBNameFrontEnd + @"].dbo.SGC_ApplicationItem
(
ID,
ApplicationName,
Item
)
VALUES
(
@ID,
@ApplicationName,
@Item
)");
SqlParameter[] param_list = new SqlParameter[]
{
new SqlParameter("@ID", SqlDbType.Char, 36, ParameterDirection.Input, false, 0, 0, "ID", DataRowVersion.Current, Guid.NewGuid().ToString()),
new SqlParameter("@ApplicationName", SqlDbType.NVarChar, 200, ParameterDirection.Input, false, 0, 0, "ApplicationName", DataRowVersion.Current, AppName),
new SqlParameter("@Item", SqlDbType.NVarChar, 500, ParameterDirection.Input, false, 0, 0, "Item", DataRowVersion.Current, Item)
};
int rowEff = ConnectDb.ExcuteNonQuery(sqlString, param_list);
return(rowEff);
}
public DataTable GetAttachment(string ID)
{
string sqlString = string.Format(@"SELECT sa.*,sdd.Title FROM [" + Config._DBNameFrontEnd + @"].dbo.SGC_Attachment sa
left join [" + Config._DBNameFrontEnd + @"].dbo.SGC_DocumentDetail sdd
on sa.DocumentID=sdd.ID
WHERE sa.DocumentID='{0}'", ID);
return(ConnectDb.ExcuteQuery(sqlString));
}
public int UpdateApp(string AppName, string NewItemText, string OldTextItem)
{
string sqlString = string.Format(@"UPDATE [" + Config._DBNameFrontEnd + @"].dbo.SGC_ApplicationItem
SET Item = N'{0}'
WHERE ApplicationName = '{1}' and Item = N'{2}'", NewItemText, AppName, OldTextItem);
int rowEff = ConnectDb.ExcuteNonQuery(sqlString);
return(rowEff);
}
/// <summary>
/// Truy vấn event
/// </summary>
/// <param name="title"></param>
/// <param name="appName"></param>
/// <param name="item"></param>
/// <returns></returns>
public DataTable LoadEventContent(string title, string appName, string item)
{
string sqlString = string.Format(@" select * from [" + Config._DBNameFrontEnd + @"].dbo.SGC_DocumentDetail dd
left join [" + Config._DBNameFrontEnd + @"].dbo.SGC_DocumentIssue di on dd.ID = di.ID
where di.AppName = N'{0}' and di.Item =N'{1}' AND di.Event = N'{2}'",
appName, item, title);
return(ConnectDb.ExcuteQuery(sqlString));
}
public DataTable LoadEventForApp(string appName, string Itemname)
{
string sqlString = string.Format(@"SELECT *
FROM [" + Config._DBNameFrontEnd + @"].dbo.SGC_DocumentIssue sdi
WHERE sdi.AppName = N'{0}'
AND sdi.Item = N'{1}' ORDER BY Event ", appName, Itemname);
return(ConnectDb.ExcuteQuery(sqlString));
}
public DataTable GetQuyenNhanVien(string id)
{
string sqlString = string.Format(@"SELECT *
FROM QuyenNhanVien qnv
WHERE qnv.MaNV = '{0}'
",
id);
return(ConnectDb.ExcuteQuery(sqlString));
}
/// <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);
}
public DataTable GetDataFeedback(string id)
{
string sqlString = string.Format(@"SELECT *
FROM [" + Config._DBNameFrontEnd + @"].dbo.SGC_Feedback fb
WHERE fb.IssueID ='{0}'
order by DateCreated",
id);
return(ConnectDb.ExcuteQuery(sqlString));
}
public bool CheckEmptyApp(string App)
{
string sqlString = string.Format(@"select ApplicationName from [" + Config._DBNameFrontEnd + "].dbo.SGC_ApplicationItem where len(ltrim(Item)) = 0 or Item is null and ApplicationName = '{0}'", App);
object kq = ConnectDb.ExcuteScalar(sqlString);
if (kq == null || kq is DBNull)
{
return(false);
}
return(true);
}
/// <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);
}
public DataTable GetCreateDocument(string id)
{
string sqlString = string.Format(@"SELECT sdi.Creator,
sdi.DateCreated,
sdi.UpdateBy,
sdi.DateModify
FROM [" + Config._DBNameFrontEnd + @"].dbo.SGC_DocumentIssue sdi
WHERE sdi.ID='{0}'",
id);
return(ConnectDb.ExcuteQuery(sqlString));
}
public int GetMaxNumber()
{
string sqlString = string.Format(@"SELECT TOP 1 CONVERT(INT, sa.ID) AS ID
FROM [" + Config._DBNameFrontEnd + @"].dbo.SGC_Attachment sa
ORDER BY CONVERT(INT, sa.ID) DESC ");
object kq = ConnectDb.ExcuteScalar(sqlString);
if (kq == null || kq is DBNull)
{
return(0);
}
return(int.Parse(Convert.ToString(kq)));
}
public DataTable GetNameCreateDoc(string id)
{
string sqlString = string.Format(@"SELECT nv.TenNV,
st.MaST,
st.TenSieuThi
FROM NhanVien nv
LEFT JOIN SieuThi st
ON nv.MaST = st.MaST
WHERE nv.MaNV='{0}'
",
id);
return(ConnectDb.ExcuteQuery(sqlString));
}
public string GetMaxdatemodify(string appname)
{
string sqlString = string.Format(@"SELECT IsNull(max(dateModify),MAX(sdi.datecreated)) AS Ngay
FROM [" + Config._DBNameFrontEnd + @"].dbo.SGC_DocumentIssue sdi
where sdi.AppName=N'{0}'
",
appname);
object kq = ConnectDb.ExcuteScalar(sqlString);
if (kq.ToString() == string.Empty)
{
return("");
}
return(Convert.ToDateTime(kq).ToString("dd-MM-yy HH:mm:ss"));
}
public DataTable SearchByID(string ID)
{
string sqlString = string.Format(@"SELECT top 10 sdd.ID,
sdd.IssueContent,
sdd.Version,
sdd.Reference,
sdd.Title
FROM [" + Config._DBNameFrontEnd + @"].dbo.SGC_DocumentIssue sdi
LEFT JOIN [" + Config._DBNameFrontEnd + @"].dbo.SGC_DocumentDetail sdd
ON sdi.ID = sdd.ID
WHERE sdi.ID='{0}' ",
ID);
return(ConnectDb.ExcuteQuery(sqlString));
}
public DataTable SearchBySelectedItem(string appname, string strSerch, string Item)
{
string sqlString = string.Format(@"SELECT top 10 sdd.ID,
sdd.IssueContent,
sdd.Version,
sdi.Event,
sdd.Reference,
sdd.Title
FROM [" + Config._DBNameFrontEnd + @"].dbo.SGC_DocumentIssue sdi
LEFT JOIN [" + Config._DBNameFrontEnd + @"].dbo.SGC_DocumentDetail sdd
ON sdi.ID = sdd.ID
WHERE sdi.Event LIKE '%{0}%' AND sdi.AppName='{1}' AND sdi.Item=N'{2}' ",
strSerch, appname, Item);
return(ConnectDb.ExcuteQuery(sqlString));
}
public DataTable SearchByDetail(string str)
{
string sqlString = string.Format(@"SELECT top 10 sdd.ID,
sdd.IssueContent,
sdd.Version,
sdd.Reference,
sdd.Title,
sdi.Event
FROM [" + Config._DBNameFrontEnd + @"].dbo.SGC_DocumentDetail sdd
left join [" + Config._DBNameFrontEnd + @"].dbo.SGC_DocumentIssue sdi
on sdd.ID=sdi.ID
WHERE sdd.IssueContent LIKE N'%{0}%'",
str);
return(ConnectDb.ExcuteQuery(sqlString));
}
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>
/// Xóa attachment
/// </summary>
/// <param name="ID"></param>
/// <returns></returns>
public bool DeleteAtt(string ID)
{
string sqlString = string.Format(@"DELETE FROM [" + Config._DBNameFrontEnd + @"].dbo.SGC_Attachment WHERE ID = '{0}'", ID);
try
{
int recEff = ConnectDb.ExcuteNonQuery(sqlString);
if (recEff > 0)
{
return(true);
}
return(false);
}
catch (Exception ex)
{
return(false);
}
}
public bool InsertTraceLog(string MaNV, DateTime LogInTime, string IP, string ReportForm, int isSuccess, string appname, string viewtype)
{
try
{
string sql =
string.Format(
@"INSERT INTO ReportLog
(
ID,
MaNV,
LogInTime,
IP,
ReportForm,
IsSuccess,
ApplicationName,
ViewType
)
VALUES
(
NEWID(),
'{0}',
@logintime,
'{1}',
'{2}',
{3},
'{4}',
'{5}'
)", MaNV, IP, ReportForm, isSuccess, appname, viewtype);
SqlParameter[] parameters = new SqlParameter[1];
SqlParameter para = new SqlParameter("@logintime", SqlDbType.DateTime);
para.Value = LogInTime;
parameters[0] = para;
int rowEff = ConnectDb.ExcuteNonQuery(sql, parameters);
}
catch (Exception exception)
{
CTLError.WriteError("InsertTraceLog", exception.Message);
return(false);
throw;
}
return(true);
}
public bool UpdateDocument_Detail(string ID, string detail, string version, string reference)
{
detail = detail.Replace("'", "''");
string sqlString = string.Format(@"UPDATE [" + Config._DBNameFrontEnd + @"].dbo.SGC_DocumentDetail
SET IssueContent = N'{0}',
[Version] = N'{2}',
Reference = N'{3}'
WHERE ID = '{1}'", detail, ID, version, reference);
try
{
if (ConnectDb.ExcuteNonQuery(sqlString) > 0 && UpdateUserModify(ID))
{
return(true);
}
return(false);
}
catch (Exception ex)
{
return(false);
}
}
/// <summary>
/// Update nội dung ngoài của event
/// </summary>
/// <param name="ID"></param>
/// <param name="detail"></param>
/// <param name="version"></param>
/// <param name="reference"></param>
/// <returns></returns>
public bool UpdateUserModify(string id)
{
string sqlString =
string.Format(
@"UPDATE [" + Config._DBNameFrontEnd + @"].dbo.SGC_DocumentIssue
SET
UpdateBy = '{0}',
DateModify = GETDATE()
where ID = '{1}'", Config._MaNV, id);
try
{
if (ConnectDb.ExcuteNonQuery(sqlString) > 0)
{
return(true);
}
return(false);
}
catch (Exception ex)
{
return(false);
}
}
public bool InsertFeedBack(string issueid, string detail, string idcreate, string attID)
{
try
{
string sql =
string.Format(
@"INSERT INTO [" + Config._DBNameFrontEnd + @"].[dbo].[SGC_Feedback]
([ID]
,[IssueID]
,[Detail]
,[Creator]
,[DateCreated]
,[IsReplyFeedback]
,[IDFeedback]
,[AttachmentID])
VALUES
(NEWID()
,'{0}'
,'{1}'
,'{2}'
,GETDATE()
,0
,''
,'{3}')", issueid, detail, idcreate, attID);
int rowEff = ConnectDb.ExcuteNonQuery(sql);
}
catch (Exception exception)
{
CTLError.WriteError("InsertFeedback", exception.Message);
return(false);
throw;
}
return(true);
}
/// <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);
}
/// <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);
}