public long AddLetterDetail(SellingRequestLetterParameter moneyRequest, string letterNo) { //DbSellingRunningService.RetrieveNextSellingRunningNo(moneyRequest.CompanyCode, moneyRequest.Year); DbCompany company = ScgDbQueryProvider.DbCompanyQuery.getDbCompanyBankAccountByCompanyCode(moneyRequest.CompanyCode); DbSellingLetterDetail letterDetail = new DbSellingLetterDetail(); letterDetail.AccountNo = company.AccountNo; letterDetail.AccountType = company.AccountType; letterDetail.BuyingDate = moneyRequest.RequestDate; letterDetail.CompanyName = company.CompanyName; letterDetail.LetterNo = letterNo; letterDetail.BankName = company.BankName; letterDetail.BankBranch = company.BankBranch; letterDetail.CreBy = UserAccount.UserID; letterDetail.CreDate = DateTime.Now; letterDetail.UpdBy = UserAccount.UserID; letterDetail.UpdDate = DateTime.Now; return(ScgDbDaoProvider.DbSellingLetterDetailDao.Save(letterDetail)); }
public int CountMoneySellingRequestByCriteria(SellingRequestLetterParameter criteria) { return(NHibernateQueryHelper.CountByCriteria(ScgDbQueryProvider.DbMoneyRequestQuery, "FindMoneySellingRequestDocument", new object[] { criteria, true, string.Empty })); }
public IList <SellingDetailResult> GetMoneySellingRequestList(SellingRequestLetterParameter criteria, int firstResult, int maxResult, string sortExpression) { return(NHibernateQueryHelper.FindPagingByCriteria <SellingDetailResult>(ScgDbQueryProvider.DbMoneyRequestQuery, "FindMoneySellingRequestDocument", new object[] { criteria, false, sortExpression }, firstResult, maxResult, sortExpression)); }
public ISQLQuery FindMoneySellingRequestDocument(SellingRequestLetterParameter param, bool isCount, string sortExpression) { StringBuilder sql = new StringBuilder(); if (isCount) { sql.Append("SELECT count(*) as MoneyRequestCount "); } else { sql.Append("SELECT SUM(b.ForeignCurrencyRemitted) as Amount, d.DocumentNo ,s.EmployeeName as RequestName,bd.LetterNo,d.DocumentID "); } sql.Append("From FnRemittance a "); sql.Append("INNER JOIN FnRemittanceItem b on a.RemittanceID = b.RemittanceID "); sql.Append("INNER JOIN Document d ON d.DocumentID = a.DocumentID "); sql.Append("INNER JOIN DbCompany c ON d.CompanyID = c.CompanyID "); sql.Append("INNER JOIN WorkFlow w ON w.DocumentID = a.DocumentID "); sql.Append("INNER JOIN SuUser s ON s.UserID = d.RequesterID "); sql.Append("LEFT JOIN DbSellingLetter bl ON bl.DocumentID = d.DocumentID "); sql.Append("LEFT JOIN DbSellingLetterDetail bd ON bl.LetterID = bd.LetterID "); sql.Append("where w.CurrentState = '21' and w.WorkFlowTypeID = '5' "); if (!string.IsNullOrEmpty(param.CompanyCode)) { sql.Append("and c.CompanyCode IN (" + param.CompanyCode + ") "); } if (!string.IsNullOrEmpty(param.LetterNo)) { sql.Append("AND bd.LetterNo = '" + param.LetterNo + "' "); } else if (!param.IsIncludeGeneratedLetter) { sql.Append("AND bd.LetterNo is null "); } if (!isCount) { sql.Append("group by d.DocumentNo,s.EmployeeName,c.CompanyCode,bd.LetterNo,d.DocumentID "); } if (!string.IsNullOrEmpty(sortExpression)) { sql.Append("Order by " + sortExpression); } else if (!isCount) { sql.Append("Order by d.DocumentNo"); } ISQLQuery query = GetCurrentSession().CreateSQLQuery(sql.ToString()); if (!isCount) { query.AddScalar("Amount", NHibernateUtil.Double); query.AddScalar("DocumentNo", NHibernateUtil.String); query.AddScalar("RequestName", NHibernateUtil.String); query.AddScalar("LetterNo", NHibernateUtil.String); query.AddScalar("DocumentID", NHibernateUtil.Int64); query.SetResultTransformer(Transformers.AliasToBean(typeof(SellingDetailResult))); } else { query.AddScalar("MoneyRequestCount", NHibernateUtil.Int32); query.UniqueResult <Int32>(); } return(query); }