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 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));
}
/// <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>
/// 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 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 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));
}
/// <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 GetQuyenNhanVien(string id)
{
string sqlString = string.Format(@"SELECT *
FROM QuyenNhanVien qnv
WHERE qnv.MaNV = '{0}'
",
id);
return(ConnectDb.ExcuteQuery(sqlString));
}
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 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 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 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));
}
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));
}