public IList <FnExpenseInvoice> GetInvoiceByExpenseID(long expenseId)
{
string sql = @" SELECT * FROM FnExpenseInvoice with (nolock) where ExpenseID = :ExpenseID and Active = 1";
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sql);
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
queryParameterBuilder.AddParameterData("ExpenseID", typeof(long), expenseId);
queryParameterBuilder.FillParameters(query);
query.AddScalar("InvoiceID", NHibernateUtil.Int64);
query.AddScalar("InvoiceNo", NHibernateUtil.String);
query.AddScalar("InvoiceDocumentType", NHibernateUtil.String);
query.AddScalar("InvoiceDate", NHibernateUtil.DateTime);
query.AddScalar("VendorCode", NHibernateUtil.String);
query.AddScalar("VendorName", NHibernateUtil.String);
query.AddScalar("Street", NHibernateUtil.String);
query.AddScalar("City", NHibernateUtil.String);
query.AddScalar("Country", NHibernateUtil.String);
query.AddScalar("PostalCode", NHibernateUtil.String);
query.AddScalar("TotalAmount", NHibernateUtil.Double);
query.AddScalar("VatAmount", NHibernateUtil.Double);
query.AddScalar("WHTAmount", NHibernateUtil.Double);
query.AddScalar("NetAmount", NHibernateUtil.Double);
query.AddScalar("Description", NHibernateUtil.String);
query.AddScalar("IsVAT", NHibernateUtil.Boolean);
query.AddScalar("IsWHT", NHibernateUtil.Boolean);
query.AddScalar("TaxID", NHibernateUtil.Int64);
query.AddScalar("NonDeductAmount", NHibernateUtil.Double);
query.AddScalar("TotalBaseAmount", NHibernateUtil.Double);
query.AddScalar("WHTRate1", NHibernateUtil.Double);
query.AddScalar("WHTTypeID1", NHibernateUtil.Int64);
query.AddScalar("BaseAmount1", NHibernateUtil.Double);
query.AddScalar("WHTAmount1", NHibernateUtil.Double);
query.AddScalar("WHTRate2", NHibernateUtil.Double);
query.AddScalar("WHTTypeID2", NHibernateUtil.Int64);
query.AddScalar("BaseAmount2", NHibernateUtil.Double);
query.AddScalar("WHTAmount2", NHibernateUtil.Double);
query.AddScalar("Active", NHibernateUtil.Boolean);
query.AddScalar("CreBy", NHibernateUtil.Int64);
query.AddScalar("CreDate", NHibernateUtil.DateTime);
query.AddScalar("UpdBy", NHibernateUtil.Int64);
query.AddScalar("UpdDate", NHibernateUtil.DateTime);
query.AddScalar("UpdPgm", NHibernateUtil.String);
query.AddScalar("WHTID1", NHibernateUtil.Int64);
query.AddScalar("WHTID2", NHibernateUtil.Int64);
query.AddScalar("VendorTaxCode", NHibernateUtil.String);
query.AddScalar("VendorBranch", NHibernateUtil.String);
query.AddScalar("BranchCode", NHibernateUtil.String);
query.AddScalar("VendorID", NHibernateUtil.Int64);
query.AddEntity("Expense", typeof(FnExpenseDocument));
//field for Rep Office
query.AddScalar("TotalAmountLocalCurrency", NHibernateUtil.Double);
query.AddScalar("TotalBaseAmountLocalCurrency", NHibernateUtil.Double);
query.AddScalar("NetAmountLocalCurrency", NHibernateUtil.Double);
query.AddScalar("ExchangeRateForLocalCurrency", NHibernateUtil.Double);
query.AddScalar("ExchangeRateMainToTHBCurrency", NHibernateUtil.Double);
query.SetResultTransformer(Transformers.AliasToBean(typeof(FnExpenseInvoice)));
return(query.List <FnExpenseInvoice>());
}
public WorkFlowStateEvent GetSendDraftStateEvent(int workFlowTypeID)
{
StringBuilder strQuery = new StringBuilder();
strQuery.AppendLine(" select workflowstateevent.* ");
strQuery.AppendLine(" from workflowstateevent ");
strQuery.AppendLine(" inner join workflowstate ");
strQuery.AppendLine(" on workflowstate.workflowstateid = workflowstateevent.workflowstateid ");
strQuery.AppendLine(" where workflowstate.workflowtypeid = :workFlowTypeID ");
strQuery.AppendLine(" and workflowstate.name = 'Draft' ");
strQuery.AppendLine(" and workflowstateevent.name = 'Send' ");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(strQuery.ToString());
query.SetInt32("workFlowTypeID", workFlowTypeID);
query.AddEntity(typeof(WorkFlowStateEvent));
IList <WorkFlowStateEvent> list = query.List <WorkFlowStateEvent>();
if (list != null)
{
return(list[0]);
}
else
{
return(null);
}
}
public IList <TaiKhoanNganHang> getHanMucTrongNgayTaiKhoanNganHang()
{
try
{
string query = @"
select ID,TenChuThe,SoTaiKhoan,
(case
when TongGiaoDich IS NULL then HanMucTrongNgay
else (HanMucTrongNgay-TongGiaoDich)
end
) HanMucTrongNgay,TrangThai
from TaiKhoanNganHang tknh
left join
(
select IDTaiKhoanNganHang,SUM(GiaTri) TongGiaoDich from DonHang
where NgayThanhToan >= CAST(CURRENT_TIMESTAMP AS DATE) and NgayThanhToan < DATEADD(DD, 1, CAST(CURRENT_TIMESTAMP AS DATE))
group by IDTaiKhoanNganHang
) temp
on tknh.ID=temp.IDTaiKhoanNganHang
";
ISQLQuery iQuery = session.CreateSQLQuery(query);
iQuery.AddEntity(typeof(TaiKhoanNganHang));
IList <TaiKhoanNganHang> listTaiKhoanNganHang = iQuery.List <TaiKhoanNganHang>();
return(listTaiKhoanNganHang);
}
catch (Exception ex)
{
return(null);
}
}
public FileWord ReadFileWordUsingWord(FileWord model)
{
Session.Clear();
ISQLQuery query = Session.CreateSQLQuery(
" SELECT *" +
" FROM TBL_FILE_WORD" +
" WHERE USR_ID = :userId" +
" AND FILE_ID = :fileId" +
" AND FILE_WD = :fileWord");
query.SetParameter("userId", model.UserId);
query.SetParameter("fileId", model.FileId);
query.SetParameter("fileWord", model.Word);
query.AddEntity(typeof(FileWord));
return((FileWord)query.UniqueResult());
/*
* return Session.QueryOver<FileWord>()
* .Where(m
* => (m.UserId == model.UserId)
* && (m.FileId == model.FileId)
* && (m.Word == model.Word))
* .SingleOrDefault<FileWord>();
*/
}
public IList <SuProgramRole> FindProgramPermission(ArrayList arrayRoleID)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append("select * from SuProgramRole where roleID ");
sqlBuilder.Append(" in (");
string strValue = "";
for (int i = 0; i < arrayRoleID.Count; i++)
{
strValue += "," + arrayRoleID[i].ToString();
}
sqlBuilder.AppendFormat("{0}", strValue.TrimStart(','));
sqlBuilder.Append(")");
ISQLQuery queryTransactionPermission = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
queryTransactionPermission.AddEntity(typeof(SuProgramRole));
IList <SuProgramRole> UserTransactionPermissionList = queryTransactionPermission.List <SuProgramRole>();
foreach (SuProgramRole programRole in UserTransactionPermissionList)
{
Console.Write(programRole.Program);
}
return(UserTransactionPermissionList);
}
public override IQueryable <TModel> GetByQuery(ISession currentSession, string queryString, IDictionary parameterDictionary)
{
global::NHibernate.IStatelessSession nativeSession = GetNativeSession(currentSession);
ISQLQuery query = nativeSession.CreateSQLQuery(queryString);
query.AddEntity(modelType);
return(GetByQuery <TModel>(query, parameterDictionary));
}
/// <summary>
/// Applies this modifier to the query.
/// </summary>
/// <param name="query">The query</param>
void IQueryModifier.Apply(IQuery query)
{
ISQLQuery sqlQuery = query as ISQLQuery;
if (sqlQuery != null)
{
sqlQuery.AddEntity(returnAlias, returnType);
}
}
public SuSession GetUserSession(long userID)
{
ISQLQuery query = GetCurrentSession().CreateSQLQuery(strGetSuSession);
query.SetInt64("UserID", userID);
query.AddEntity(typeof(SuSession));
IList <SuSession> suSession = query.List <SuSession>();
return(suSession.Count > 0 ? suSession[0] : null);
}
/// <summary>
///
/// </summary>
/// <param name="sql"></param>
/// <param name="returnAlias"></param>
/// <param name="returnClass"></param>
/// <returns></returns>
public override ISQLQuery findBySql(string sql, string[] returnAlias, Type[] returnClass)
{
ISQLQuery result = session.CreateSQLQuery(sql);
for (int i = 0; i < returnAlias.Length; i++)
{
result.AddEntity(returnAlias[i], returnClass[i]);
}
return(result);
}
public IList <DbParameterGroup> GetParameterGroupByGroupNo(string groupNo)
{
string strSQL = " SELECT * FROM DbParameterGroup WHERE GroupNo = :GroupNo ";
ISQLQuery query = GetCurrentSession().CreateSQLQuery(strSQL);
query.SetInt16("GroupNo", Utilities.ParseShort(groupNo));
query.AddEntity(typeof(DTO.DbParameter));
IList <DbParameterGroup> dbParameterGroup = query.List <DbParameterGroup>();
return(dbParameterGroup);
}
public IList <DTO.WorkFlow> GetAllActiveWorkFlow()
{
StringBuilder strQuery = new StringBuilder();
strQuery.AppendLine("SELECT wf.*");
strQuery.AppendLine("FROM WorkFlow wf ");
strQuery.AppendLine("INNER JOIN WorkFlowState wfs ON wf.CurrentState = wfs.WorkFlowStateID AND wf.WorkFlowTypeID = wfs.WorkFlowTypeID ");
strQuery.AppendLine("WHERE wfs.Name NOT IN ('Complete' , 'Cancel') ");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(strQuery.ToString());
query.AddEntity(typeof(DTO.WorkFlow));
return(query.List <DTO.WorkFlow>());
}
public IList <HtmlModel> ReadHtmlList(String userId)
{
ISQLQuery query = Session.CreateSQLQuery(
" SELECT *" +
" FROM TBL_HTML" +
" WHERE USR_ID = :userId" +
" ORDER BY USR_ID, HTML_ID");
query.SetParameter("userId", userId);
query.AddEntity(typeof(HtmlModel));
return(query.List <HtmlModel>());
}
/// <summary>
/// Is used SQL query instead of queryOver because we cannot form instance on Domain.Entity.Part class with defined id
/// </summary>
public IList <InspectionTestResult> GetByPartId(Guid partId)
{
ISQLQuery query = session.CreateSQLQuery(
@"select i.* " +
"from InspectionTestResult i " +
"where i.partId = :PartId");
query.SetString("PartId", partId.ToString());
query.AddEntity(typeof(InspectionTestResult));
IList <InspectionTestResult> results = query.List <InspectionTestResult>();
return(results);
}
public IList <DonHang> getDonHang()
{
try
{
string query = @"select * from DonHang ";
ISQLQuery iQuery = session.CreateSQLQuery(query);
iQuery.AddEntity(typeof(DonHang));
IList <DonHang> listDonHang = iQuery.List <DonHang>();
return(listDonHang);
}
catch (Exception ex)
{
return(null);
}
}
public IList <LoaiTien> getLoaiTien()
{
try
{
string query = @"select * from LoaiTien ";
ISQLQuery iQuery = session.CreateSQLQuery(query);
iQuery.AddEntity(typeof(LoaiTien));
IList <LoaiTien> listLoaiTien = iQuery.List <LoaiTien>();
return(listLoaiTien);
}
catch (Exception ex)
{
return(null);
}
}
public IList <FileIOLog> ReadFileIOLogList(FileModel model)
{
ISQLQuery query = Session.CreateSQLQuery(
" SELECT *" +
" FROM TBL_FILE_IO_LOG" +
" WHERE USR_ID = :userId" +
" AND FILE_ID = :fileId" +
" ORDER BY USR_ID, FILE_ID");
query.SetParameter("userId", model.UserId);
query.SetParameter("fileId", model.FileId);
query.AddEntity(typeof(FileIOLog));
return(query.List <FileIOLog>());
}
public IList <HtmlWord> ReadHtmlWordList(HtmlModel model)
{
ISQLQuery query = Session.CreateSQLQuery(
" SELECT *" +
" FROM TBL_HTML_WORD" +
" WHERE USR_ID = :userId" +
" AND HTML_ID = :htmlId" +
" ORDER BY USR_ID, HTML_ID");
query.SetParameter("userId", model.UserId);
query.SetParameter("htmlId", model.HtmlId);
query.AddEntity(typeof(HtmlWord));
return(query.List <HtmlWord>());
}
IQuery ISession.CreateSQLQuery(string sql, string[] returnAliases, Type[] returnClasses)
{
if (returnAliases.Length != returnClasses.Length)
{
throw new ArgumentException(" returnAliases's length not equal returnClasses's length");
}
ISQLQuery query = inner.CreateSQLQuery(sql);
for (int i = 0; i < returnClasses.Length; i++)
{
query.AddEntity(returnAliases[i], returnClasses[i]);
}
return(query);
}
public IList <LogRutTienHoaHong> getLogRutTienHoaHong(string condition)
{
try
{
string query = @"select * from LogRutTienHoaHong " + condition;
ISQLQuery iQuery = session.CreateSQLQuery(query);
iQuery.AddEntity(typeof(LogRutTienHoaHong));
IList <LogRutTienHoaHong> listLogRutTienHoaHong = iQuery.List <LogRutTienHoaHong>();
return(listLogRutTienHoaHong);
}
catch (Exception ex)
{
return(null);
}
}
public IList <TinTuc> getTinTuc(string condition)
{
try
{
string query = @"select * from TinTuc " + condition;
ISQLQuery iQuery = session.CreateSQLQuery(query);
iQuery.AddEntity(typeof(TinTuc));
IList <TinTuc> listTinTuc = iQuery.List <TinTuc>();
return(listTinTuc);
}
catch (Exception ex)
{
return(null);
}
}
public bool xacThucNguoiDung(string tenDangNhap, string password)
{
try
{
string query = @"select * from NguoiDung where TenDangNhap=N'" + tenDangNhap + "' and MatKhau=N'" + password + "'";
ISQLQuery iQuery = session.CreateSQLQuery(query);
iQuery.AddEntity(typeof(NguoiDung));
IList <NguoiDung> listNguoiDung = iQuery.List <NguoiDung>();
return((listNguoiDung == null || listNguoiDung.Count == 0) ? false : true);
}
catch (Exception ex)
{
return(false);
}
}
public IList <TaiKhoan_DonVi> getTaiKhoan_DonVi(string condition)
{
try
{
string query = @"select * from TaiKhoan_DonVi " + condition;
ISQLQuery iQuery = session.CreateSQLQuery(query);
iQuery.AddEntity(typeof(TaiKhoan_DonVi));
IList <TaiKhoan_DonVi> listTaiKhoan_DonVi = iQuery.List <TaiKhoan_DonVi>();
return(listTaiKhoan_DonVi);
}
catch (Exception ex)
{
return(null);
}
}
public IList <CKTD_TaiKhoan> getCKTD_TaiKhoan()
{
try
{
string query = @"select * from CKTD_TaiKhoan ";
ISQLQuery iQuery = session.CreateSQLQuery(query);
iQuery.AddEntity(typeof(CKTD_TaiKhoan));
IList <CKTD_TaiKhoan> listCKTD_TaiKhoan = iQuery.List <CKTD_TaiKhoan>();
return(listCKTD_TaiKhoan);
}
catch (Exception ex)
{
return(null);
}
}
public IList <CKTD_DichVuCong> getCKTD_DichVuCong(string condition)
{
try
{
string query = @"select * from CKTD_DichVuCong " + condition;
ISQLQuery iQuery = session.CreateSQLQuery(query);
iQuery.AddEntity(typeof(CKTD_DichVuCong));
IList <CKTD_DichVuCong> listCKTD_DichVuCong = iQuery.List <CKTD_DichVuCong>();
return(listCKTD_DichVuCong);
}
catch (Exception ex)
{
return(null);
}
}
public IList <CKTD_DonVi> getCKTD_DonVi()
{
try
{
string query = @"select * from CKTD_DonVi ";
ISQLQuery iQuery = session.CreateSQLQuery(query);
iQuery.AddEntity(typeof(CKTD_DonVi));
IList <CKTD_DonVi> listCKTD_DonVi = iQuery.List <CKTD_DonVi>();
return(listCKTD_DonVi);
}
catch (Exception ex)
{
return(null);
}
}
public bool xacThucTaiKhoan(string TaiKhoan, string password)
{
try
{
string query = @"select * from CKTD_TaiKhoan where TaiKhoan=N'" + TaiKhoan + "' and MatKhau=N'" + password + "'";
ISQLQuery iQuery = session.CreateSQLQuery(query);
iQuery.AddEntity(typeof(CKTD_TaiKhoan));
IList <CKTD_TaiKhoan> listNguoiDung = iQuery.List <CKTD_TaiKhoan>();
return((listNguoiDung == null || listNguoiDung.Count == 0) ? false : true);
}
catch (Exception ex)
{
return(false);
}
}
public IList <TaiKhoan_DonVi> getTaiKhoan_DonVi(string condition, string fieldAndTypeOrder, int pageId, int pageSize)
{
try
{
string query = @"select * from (
select *,ROW_NUMBER() OVER (ORDER BY " + fieldAndTypeOrder + " ) STT from TaiKhoan_DonVi " + condition + @"
) resultTable where resultTable.STT>" + ((pageId - 1) * pageSize) + " and resultTable.STT<=" + (pageId * pageSize);
ISQLQuery iQuery = session.CreateSQLQuery(query);
iQuery.AddEntity(typeof(TaiKhoan_DonVi));
IList <TaiKhoan_DonVi> listTaiKhoan_DonVi = iQuery.List <TaiKhoan_DonVi>();
return(listTaiKhoan_DonVi);
}
catch (Exception ex)
{
return(null);
}
}
public IList <TaiKhoan_DonVi> getTaiKhoan_DonVi(string condition, int pageIndex, int pageSize)
{
int num = pageIndex * pageSize - pageSize;
try
{
string query = "SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY ID desc) as row FROM TaiKhoan_DonVi " + condition + " ) a WHERE a.row > " + num + " and a.row <= " + (num + pageSize);
ISQLQuery iQuery = session.CreateSQLQuery(query);
iQuery.AddEntity(typeof(TaiKhoan_DonVi));
IList <TaiKhoan_DonVi> listTaiKhoan_DonVi = iQuery.List <TaiKhoan_DonVi>();
return(listTaiKhoan_DonVi);
}
catch (Exception ex)
{
return(null);
}
}
public IList <CKTD_VanBan> getCKTD_VanBan(string condition, int pageIndex, int pageSize)
{
int num = pageIndex * pageSize - pageSize;
try
{
string query = "SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY ID desc) as row FROM CKTD_VanBan " + condition + " ) a WHERE a.row > " + num + " and a.row <= " + (num + pageSize);
ISQLQuery iQuery = session.CreateSQLQuery(query);
iQuery.AddEntity(typeof(CKTD_VanBan));
IList <CKTD_VanBan> listCKTD_VanBan = iQuery.List <CKTD_VanBan>();
return(listCKTD_VanBan);
}
catch (Exception ex)
{
return(null);
}
}
public SuUserRole FindUserRoleByUserRoleId(long id)
{
ISQLQuery query = GetCurrentSession().CreateSQLQuery("SELECT * FROM SuUserRole WHERE [ID] = :id ");
query.SetInt64("id", id);
query.AddEntity(typeof(SuUserRole));
IList <SuUserRole> user = query.List <SuUserRole>();
if (user.Count > 0)
{
return(user[0]);
}
else
{
return(null);
}
}
