public GlobalTranslate ResolveMessage(string programCode, string translateSymbol, string languageCode)
{
StringBuilder strQuery = new StringBuilder();
strQuery.AppendLine(" SELECT TranslateWord ");
strQuery.AppendLine(" FROM SuGlobalTranslate g ");
strQuery.AppendLine(" INNER JOIN SuGlobalTranslateLang gl ");
strQuery.AppendLine(" ON g.TranslateID = gl.TranslateID ");
strQuery.AppendLine(" INNER JOIN DbLanguage l ");
strQuery.AppendLine(" ON gl.LanguageID = l.LanguageID ");
strQuery.AppendLine(" WHERE l.LanguageCode = :LanguageCode ");
strQuery.AppendLine(" AND g.TranslateSymbol = :TranslateSymbol ");
strQuery.AppendLine(" AND g.ProgramCode = :ProgramCode ");
strQuery.AppendLine(" AND g.TranslateControl is null ");
strQuery.AppendLine(" AND g.Active = 1 ");
strQuery.AppendLine(" AND gl.Active = 1 ");
strQuery.AppendLine(" AND l.Active = 1 ");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(strQuery.ToString());
query.SetString("LanguageCode", languageCode);
query.SetString("TranslateSymbol", translateSymbol);
query.SetString("ProgramCode", programCode);
query.AddScalar("TranslateWord", NHibernateUtil.String);
GlobalTranslate globalTranslate = (GlobalTranslate)query.SetResultTransformer(Transformers.AliasToBean(typeof(GlobalTranslate))).UniqueResult();
return(globalTranslate);
}
//update loan status to Paid when all installment have been paid
public void UpdateLoanToPaid(string loanId)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine(@"
update l
set loan_status = :loanStatus
from t_loan l,
(
select l.loan_id, count(i.installment_id) count_ins
from dbo.T_LOAN l
left join dbo.T_INSTALLMENT i
on l.loan_id = i.loan_id and i.installment_status = :insStatus
where l.loan_id = :loanId
group by l.loan_id
having count(i.installment_id) = 0
) i
where l.loan_id = i.loan_id and i.count_ins = 0 and l.loan_status = :currentLoanStatus and l.loan_id = :loanId;
");
ISQLQuery q = Session.CreateSQLQuery(sql.ToString());
q.SetString("loanId", loanId);
q.SetString("loanStatus", EnumLoanStatus.Paid.ToString());
q.SetString("insStatus", EnumInstallmentStatus.Not_Paid.ToString());
q.SetString("currentLoanStatus", EnumLoanStatus.OK.ToString());
q.ExecuteUpdate();
}
public string GetResolveControl(string programCode, string translateControl, string languageCode)
{
StringBuilder strQuery = new StringBuilder();
strQuery.AppendLine(" SELECT TranslateWord ");
strQuery.AppendLine(" FROM SuGlobalTranslate g ");
strQuery.AppendLine(" INNER JOIN SuGlobalTranslateLang gl ");
strQuery.AppendLine(" ON g.TranslateID = gl.TranslateID ");
strQuery.AppendLine(" INNER JOIN DbLanguage l ");
strQuery.AppendLine(" ON gl.LanguageID = l.LanguageID ");
strQuery.AppendLine(" WHERE l.LanguageCode = :LanguageCode ");
strQuery.AppendLine(" AND g.TranslateControl = :TranslateControl ");
strQuery.AppendLine(" AND g.ProgramCode = :ProgramCode ");
strQuery.AppendLine(" AND g.Active = 1 ");
strQuery.AppendLine(" AND gl.Active = 1 ");
strQuery.AppendLine(" AND l.Active = 1 ");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(strQuery.ToString());
query.SetString("LanguageCode", languageCode);
query.SetString("TranslateControl", translateControl);
query.SetString("ProgramCode", programCode);
query.AddScalar("TranslateWord", NHibernateUtil.String);
return(query.UniqueResult <string>());
}
public IList <GlobalTranslate> LoadProgramResources(string programCode, string languageCode)
{
StringBuilder strQuery = new StringBuilder();
strQuery.AppendLine(" SELECT TranslateControl , TranslateWord ");
strQuery.AppendLine(" FROM SuGlobalTranslate g ");
strQuery.AppendLine(" INNER JOIN SuGlobalTranslateLang gl ");
strQuery.AppendLine(" ON g.TranslateID = gl.TranslateID ");
strQuery.AppendLine(" INNER JOIN DbLanguage l ");
strQuery.AppendLine(" ON gl.LanguageID = l.LanguageID ");
strQuery.AppendLine(" WHERE l.LanguageCode = :LanguageCode ");
strQuery.AppendLine(" AND g.ProgramCode = :ProgramCode ");
strQuery.AppendLine(" AND g.Active = 1 ");
strQuery.AppendLine(" AND gl.Active = 1 ");
strQuery.AppendLine(" AND l.Active = 1 ");
strQuery.AppendLine(" AND isnull(g.TranslateControl,'') <> '' ");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(strQuery.ToString());
query.SetString("LanguageCode", languageCode);
query.SetString("ProgramCode", programCode);
query.AddScalar("TranslateControl", NHibernateUtil.String);
query.AddScalar("TranslateWord", NHibernateUtil.String);
IList <GlobalTranslate> globalTranslates = query.SetResultTransformer(Transformers.AliasToBean(typeof(GlobalTranslate))).List <GlobalTranslate>();
return(globalTranslates);
}
public void SyncUpdateCostCenter(string costCenterCode)
{
ISQLQuery query = GetCurrentSession().CreateSQLQuery("exec [dbo].[SyncUpdateCostCenterData] :costCenterCode ");
query.SetString("costCenterCode", costCenterCode);
query.AddScalar("count", NHibernateUtil.Int32).UniqueResult();
}
public void SyncDeleteLocation(string locationCode)
{
ISQLQuery query = GetCurrentSession().CreateSQLQuery("exec [dbo].[SyncDeleteLocationData] :locationCode ");
query.SetString("locationCode", locationCode);
query.AddScalar("count", NHibernateUtil.Int32).UniqueResult();
}
public void setActiveAll(bool active, bool eccflag, string aliasName)
{
string sql = "UPDATE DbCostCenter SET Active = ? from DbCompany ";
if (eccflag)
{
sql += " inner join DbSapInstance on DbCompany.SAPCode = DbSapInstance.Code ";
}
sql += " where DbCostCenter.CompanyID = DbCompany.CompanyID and DbCompany.UseEcc = ? ";
if (eccflag)
{
sql += " and DbSapInstance.AliasName = ? ";
}
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sql);
query.SetBoolean(0, active);
query.SetBoolean(1, eccflag);
if (eccflag)
{
query.SetString(2, aliasName);
}
query.AddScalar("count", NHibernateUtil.Int32).UniqueResult();
}
public IList <SuRTENodeSearchResult> GetRTEContent(short languageId, string nodetype, short nodeId)
{
StringBuilder strQuery = new StringBuilder();
strQuery.AppendLine(" SELECT n.NodeId,n.NodeHeaderId,n.NodeOrderNo,c.languageId,n.NodeType,c.Header,c.Content,n.ImagePath");
strQuery.AppendLine(" From SuRTENode n LEFT OUTER JOIN SuRTEContent c on c.nodeid = n.nodeid ");
strQuery.AppendLine(" WHERE c.LanguageID = :LanguageID and n.nodetype = :NodeType ");
strQuery.AppendLine(" AND n.Active = 'TRUE' and n.NodeId = :NodeId");
strQuery.AppendLine(" ORDER BY n.NodeHeaderId,n.NodeOrderNo");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(strQuery.ToString());
query.SetInt16("LanguageID", languageId);
query.SetString("NodeType", nodetype);
query.SetInt16("NodeId", nodeId);
query.AddScalar("NodeId", NHibernateUtil.Int16);
query.AddScalar("NodeHeaderId", NHibernateUtil.Int16);
query.AddScalar("NodeOrderNo", NHibernateUtil.Int16);
query.AddScalar("languageId", NHibernateUtil.Int16);
query.AddScalar("NodeType", NHibernateUtil.String);
query.AddScalar("Header", NHibernateUtil.String);
query.AddScalar("Content", NHibernateUtil.String);
query.AddScalar("ImagePath", NHibernateUtil.String);
IList <SuRTENodeSearchResult> list = query.SetResultTransformer(Transformers.AliasToBean(typeof(SuRTENodeSearchResult))).List <SuRTENodeSearchResult>();
return(list);
}
public void SyncDeleteIO(string iONumber)
{
ISQLQuery query = GetCurrentSession().CreateSQLQuery("exec [dbo].[SyncDeleteInternalOrderData] :IONumber ");
query.SetString("IONumber", iONumber);
query.AddScalar("count", NHibernateUtil.Int32).UniqueResult();
}
public void SyncDeleteCompany(string CompanyCode)
{
ISQLQuery query = GetCurrentSession().CreateSQLQuery("exec [dbo].[SyncDeleteCompanyData] :CompanyCode ");
query.SetString("CompanyCode", CompanyCode);
query.AddScalar("count", NHibernateUtil.Int32).UniqueResult();
}
/// <summary>
/// returns a list of byte[] containing all
/// pfds for the given customer id
/// </summary>
/// <param name="customerID">the id of the customer</param>
public static List <byte[]> GetAllRechnungenForKunde(int customerID)
{
try
{
List <byte[]> ret = new List <byte[]>();
using (IRepository repository = RepositoryFactory.Instance.CreateRepository <Repository>())
{
ISQLQuery query = repository.GetQuery("select text from " + TABLERECHNUNGDOCS + " r where r.title like ?");
query.SetString(0, "%" + customerID + "%");
query.AddScalar("text", NHibernateUtil.BinaryBlob);
var all = query.List();
foreach (var s in all)
{
ret.Add(s as byte[]);
}
}
return(ret);
}
catch (DatabaseException)
{
throw;
}
catch (Exception ex)
{
throw (new DatabaseException(ex, ""));
}
}
//private static IRepository repository = null;
#endregion
/// <summary>
/// inserts the <see cref="BenutzerverwaltungBL.Model.DataObjects.Rechnung"/> as pdf in the db
/// the titel is genrated out of the details of the <see cref="BenutzerverwaltungBL.Model.DataObjects.Rechnung"/>
/// throws an exception if an error occurs
/// </summary>
/// <param name="rechnungsId">the identifier of the <see cref="BenutzerverwaltungBL.Model.DataObjects.Rechnung"/></param>
/// <returns>true or throws an exception</returns>
public static bool InsertRechnungAsDoc(int rechnungsId)
{
try
{
bool ret = true;
Rechnung rechnungn;
using (IRepository repository = RepositoryFactory.Instance.CreateRepository <Repository>())
{
rechnungn = repository.SelectSingle <Rechnung>(DetachedCriteria.For <Rechnung>()
.Add(Restrictions.IdEq(rechnungsId)));
ISQLQuery query = repository.GetQuery("insert into " + TABLERECHNUNGDOCS + "(Title,Text) values (?,?)");
query.SetString(0, GenerateTitel(rechnungn));
query.SetParameter(1, GeneratePDF(rechnungn), NHibernateUtil.BinaryBlob);
query.ExecuteUpdate();
rechnungn.IsAlreadyPdf = true;
repository.SaveOrUpdate <Rechnung>(rechnungn);
}
return(ret);
}
catch (DatabaseException)
{
throw;
}
catch (Exception ex)
{
throw (new DatabaseException(ex, "Fehler beim ausstellen der Rechnung", "Überprüfen Sie ob die Rechnung bereits aussgestellt wurde", "Wenn Sie keine Lösung finden, melden Sie sich bei unserem Service Team.", "Danke"));
}
}
/// <summary>
/// selects a certain <see cref="BenutzerverwaltungBL.Model.DataObjects.Rechnung"/> of the customer given
/// by checking the date given
/// </summary>
/// <param name="customerID">the customer whos <see cref="BenutzerverwaltungBL.Model.DataObjects.Rechnung"/> to select</param>
/// <param name="rechnungsID">the id of the <see cref="BenutzerverwaltungBL.Model.DataObjects.Rechnung"/></param>
/// <returns> a byte [] or null or throws an exception </returns>
public static byte[] GetCertainRechnungForKunde(int customerID, int rechnungsID)
{
try
{
byte[] ret = null;
using (IRepository repository = RepositoryFactory.Instance.CreateRepository <Repository>())
{
Rechnung r = repository.SelectSingle <Rechnung>(DetachedCriteria.For <Rechnung>()
.Add(Restrictions.IdEq(rechnungsID)));
ISQLQuery query = repository.GetQuery("select text from " + TABLERECHNUNGDOCS + " r where r.title like ?");
query.SetString(0, customerID + "%" + r.Rechnungsdatum.ToShortDateString());
query.AddScalar("text", NHibernateUtil.BinaryBlob);
ret = query.UniqueResult() as byte[];
}
return(ret);
}
catch (DatabaseException)
{
throw;
}
catch (Exception ex)
{
throw (new DatabaseException(ex, "Keine Rechnung vorhanden!"));
}
}
public void SyncDeleteUser(string userName)
{
ISQLQuery query = GetCurrentSession().CreateSQLQuery("exec [dbo].[SyncDeleteUserData] :userName ");
query.SetString("userName", userName);
query.AddScalar("count", NHibernateUtil.Int32).UniqueResult();
}
public ISQLQuery FindSuRoleLangSearchResult(SuRoleLangSearchResult roleLangSearchResult, short languageID, string roleName, long userID)
{
StringBuilder strQuery = new StringBuilder();
strQuery.AppendLine(" SELECT r.RoleID as RoleId, rl.RoleName as RoleName ");
strQuery.AppendLine(" , lang.LanguageID as LanguageId, lang.LanguageName as LanguageName ");
strQuery.AppendLine(" , r.Active as Active ");
strQuery.AppendLine(" FROM SuRole r ");
strQuery.AppendLine(" INNER JOIN SuRoleLang rl ");
strQuery.AppendLine(" ON rl.RoleID = r.RoleID AND rl.LanguageID = :languageID ");
strQuery.AppendLine(" INNER JOIN DbLanguage lang ");
strQuery.AppendLine(" ON lang.LanguageID = :languageID ");
strQuery.AppendLine(" WHERE rl.RoleName LIKE :roleName ");
strQuery.AppendLine(" AND r.Active = 'true' ");
strQuery.AppendLine(" AND r.RoleID NOT IN ");
strQuery.AppendLine(" (SELECT ur.RoleID FROM SuUserRole ur WHERE ur.UserID = :userID) ");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(strQuery.ToString());
query.SetInt16("languageID", languageID);
query.SetString("roleName", "%" + roleName + "%");
query.SetInt64("userID", userID);
query.AddScalar("RoleId", NHibernateUtil.Int16);
query.AddScalar("RoleName", NHibernateUtil.String);
query.AddScalar("LanguageId", NHibernateUtil.Int16);
query.AddScalar("LanguageName", NHibernateUtil.String);
query.AddScalar("Active", NHibernateUtil.Boolean);
query.SetResultTransformer(Transformers.AliasToBean(typeof(SuRoleLangSearchResult)));
return query;
}
/// <summary>
/// 获取周报收件人列表
/// Created:2017.04.11(ChengMengjia)
/// </summary>
/// <returns></returns>
public IList <dynamic> GetMemberList(string ProjectID, string IDs)
{
string sql = "select * from Stakeholders where PID=:PID and ID in (" + IDs + ")";
ISQLQuery query = NHHelper.GetCurrentSession().CreateSQLQuery(sql.ToString());
query.SetString("PID", ProjectID);
return(query.DynamicList());
}
private void updateGroupBox()
{
string sql = "select count(*) from xcar_customer where upload_time like ? and upload_ind='Y'";
ISQLQuery isql = NHibernateHelper.CreateSQLQuery(sql);
isql.SetString(0, DateTime.Now.ToString("yyyy-MM-dd") + "%");
//int count = (int)isql.UniqueResult();
groupBox1.Text = string.Format("今天已成功上传 {0} 个客户", isql.UniqueResult().ToString());
}
public void Copy(int YearFrom, int YearTo, string type)
{
ISQLQuery query = GetCurrentSession().CreateSQLQuery("exec [dbo].[CopyHolidayProfile] :yearFrom , :yearTo , :type ");
query.SetInt32("yearFrom", YearFrom);
query.SetInt32("yearTo", YearTo);
query.SetString("type", type);
query.AddScalar("count", NHibernateUtil.Int32).UniqueResult();
}
public IList <string> CheckDuplicateDocumentID(string documentIDs)
{
string sql = @" select d.DocumentNo as DocumentNo from dbo.DbSellingLetter letter
inner join Document d on d.DocumentID = letter.DocumentID
where letter.DocumentID in (select item from dbo.fnSplit(:documentList,',')) ";
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sql);
query.SetString("documentList", documentIDs);
query.AddScalar("DocumentNo", NHibernateUtil.String);
return(query.List <string>());
}
public IList <Int32> GetYear(string type)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append(" select Year ");
sqlBuilder.Append(" from DbHolidayProfile WHERE Type = :type Order By Year");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
query.SetString("type", type);
query.AddScalar("Year", NHibernateUtil.Int32);
IList <DbHolidayProfile> year = query.SetResultTransformer(Transformers.AliasToBean(typeof(DbHolidayProfile))).List <DbHolidayProfile>();
return(year.Select(t => t.Year).ToList());
}
/// <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 <WorkFlowStateEvent> FindWorkFlowStateEvent(string WorkFlowStateName, string WorkFlowStateEventName, int WorkFlowTypeID)
{
StringBuilder strQuery = new StringBuilder();
strQuery.AppendLine(" SELECT t2.WorkFlowStateEventID AS WorkFlowStateEventID ");
strQuery.AppendLine(" FROM WorkFlowState t1 ");
strQuery.AppendLine(" INNER JOIN WorkFlowStateEvent t2 ");
strQuery.AppendLine(" ON t1.WorkFlowStateID = t2.WorkFlowStateID ");
strQuery.AppendLine(" WHERE t1.[Name] = :WorkFlowStateEventName ");
strQuery.AppendLine(" AND t2.[Name] = :WorkFlowStateName ");
strQuery.AppendLine(" AND t1.WorkFlowTypeID = :WorkFlowTypeID ");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(strQuery.ToString());
query.SetString("WorkFlowStateName", WorkFlowStateName);
query.SetString("WorkFlowStateEventName", WorkFlowStateEventName);
query.SetInt32("WorkFlowTypeID", WorkFlowTypeID);
query.AddScalar("WorkFlowStateEventID", NHibernateUtil.Int32);
query.SetResultTransformer(Transformers.AliasToBean(typeof(WorkFlowStateEvent)));
return(query.List <WorkFlowStateEvent>());
}
public CurrentIdentity GetCurrentIdentity(string tableName)
{
using (ISession session = m_SessionFactory.OpenSession())
{
// Create a criteria object with the specified criteria
string sqlStr = "SELECT IDENT_CURRENT(:tableName) as Value";
ISQLQuery sqlQuery = session.CreateSQLQuery(sqlStr);
sqlQuery.AddScalar("Value", NHibernateUtil.Int64);
sqlQuery.SetString("tableName", tableName);
sqlQuery.SetResultTransformer(Transformers.AliasToBean(typeof(CurrentIdentity)));
// Set return value
return(sqlQuery.UniqueResult <CurrentIdentity>());
}
}
private void cobProvince_SelectedIndexChanged(object sender, EventArgs e)
{
string province = this.cobProvince.Text;
string sql = "select DISTINCT(city) from xcar_shop where province=?";
ISQLQuery isql = NHibernateHelper.CreateSQLQuery(sql);
isql.SetString(0, province);
var ds = isql.List <string>();
this.cobCity.Items.Clear();
foreach (var item in ds)
{
this.cobCity.Items.Add(item);
}
this.cobCity.SelectedIndex = 0;
}
private void cobCity_SelectedIndexChanged(object sender, EventArgs e)
{
string city = this.cobCity.Text;
string sql = "select name from xcar_shop where city=?";
ISQLQuery isql = NHibernateHelper.CreateSQLQuery(sql);
isql.SetString(0, city);
var ds = isql.List <string>();
this.cobShop.Items.Clear();
foreach (var item in ds)
{
this.cobShop.Items.Add(item);
}
this.cobShop.SelectedIndex = 0;
}
public static List <dynamic> GetDynamicList(string sql, Dictionary <string, string> args)
{
ISession session = GetCurrentSession();
try
{
ISQLQuery query = session.CreateSQLQuery(sql);
foreach (var item in args)
{
query.SetString(item.Key, item.Value);
}
return(query.DynamicList().ToList());
}
catch (Exception ex)
{
LogHelper.WriteException(ex, LogType.DataAccessDLL);
return(null);
}
}
public bool CheckDay(DateTime checkDay, string type)
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT count(*) as Count FROM DbHolidayProfile hp INNER JOIN DbHoliday h ON hp.Id = h.HolidayProfileId where hp.IsApprove = 1 AND hp.Year = :Year AND h.Date = :Date AND hp.Type = :Type");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sql.ToString());
query.SetInt32("Year", checkDay.Year);
query.SetDateTime("Date", checkDay.Date);
query.SetString("Type", type);
query.AddScalar("Count", NHibernateUtil.Int32);
int count = query.UniqueResult <Int32>();
if (count > 0 || (checkDay.DayOfWeek == DayOfWeek.Saturday || checkDay.DayOfWeek == DayOfWeek.Sunday))
{
return(true);
}
return(false);
}
public bool CheckDuplicateCostCenterCodeFromTmpCostCenter(string costCenterCode)
{
// update by Sirilak 06-Nov-2009
StringBuilder sql = new StringBuilder();
sql.Append("SELECT count(*) as Count FROM Tmp_DbCostCenter WHERE CostCenterCode = :CostCenterCode ");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sql.ToString());
query.SetString("CostCenterCode", costCenterCode);
query.AddScalar("Count", NHibernateUtil.Int32);
int count = query.UniqueResult <int>();
if (count > 0)
{
return(true);
}
return(false);
}
public IList <SuProgramRole> FindByProgramCode_UserID(string programCode, long userID)
{
ISQLQuery query = GetCurrentSession().CreateSQLQuery(@"
select SuProgramRole.*
from SuProgramRole
inner join SuUserRole
on SuProgramRole.RoleID = SuUserRole.RoleID
inner join SuProgram
on SuProgramRole.ProgramID = SuProgram.ProgramID
where SuProgram.ProgramCode = :ProgramCode
and SuUserRole.UserID = :UserID
");
//and SuProgramRole.Active = 1
//and SuUserRole.Active = 1
query.SetString("ProgramCode", programCode);
query.SetInt64("UserID", userID);
query.AddEntity(typeof(SuProgramRole));
return(query.List <SuProgramRole>());
}
/// <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)
{
try
{
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);
}
catch (GenericADOException ex)
{
throw new RepositoryException("GetByPartId", ex);
}
}