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);
}