public int RequestCount()
{
VOPB criteria = GetPBCriteria();
int count = ScgDbQueryProvider.DbPBQuery.CountPbByCriteria(criteria);
return(count);
}
protected int RequestCount()
{
VOPB criteria = GetPBCriteria();
int count = ScgDbQueryProvider.DbPbRateQuery.CountPbByCriteria(criteria);
return(count);
}
public VOPB GetPBCriteria()
{
VOPB pb = new VOPB();
pb.UserID = UserAccount.UserID;
pb.LanguageID = UserAccount.CurrentLanguageID;
return(pb);
}
public VOPB GetPBCriteria()
{
VOPB pb = new VOPB();
pb.PBCode = ctlPBCode.Text;
pb.Description = ctrDescription.Text;
return(pb);
}
protected void ctlPrintAllBtn_Click(object sender, EventArgs e)
{
string key = ApplicationMode + "reimbursementCookie_" + UserAccount.UserID;
Request.Cookies.Remove(key);
HttpCookie reimburstCookie = Request.Cookies[key];
if (reimburstCookie == null)
{
reimburstCookie = new HttpCookie(key);
}
string markList = string.Empty;
string unMarkList = string.Empty;
string pb = string.Empty;
VOPB voPb = ScgDbQueryProvider.DbPBQuery.GetDescription(Convert.ToInt64(ctlPbDropdownList.SelectedValue), 2);
pb = voPb.PBCode + " " + voPb.Description;
foreach (GridViewRow item in ctlReport.Rows)
{
if (item.RowType == DataControlRowType.DataRow)
{
HiddenField docNo = (HiddenField)ctlReport.Rows[item.RowIndex].FindControl("ctlDocumentID");
CheckBox mark = (CheckBox)ctlReport.Rows[item.RowIndex].FindControl("ctlMark");
Literal requestNo = (Literal)ctlReport.Rows[item.RowIndex].FindControl("ctlRequestNo");
if (!string.IsNullOrEmpty(markList))
{
markList += ",";
}
markList += requestNo.Text;
}
}
reimburstCookie.Values["markList"] = markList;
reimburstCookie.Values["unmarkList"] = unMarkList;
Response.Cookies.Add(reimburstCookie);
StringBuilder scriptBuilder = new StringBuilder("window.open('ReimbursementReportOutput.aspx?");
scriptBuilder.AppendFormat("pbId={0}", ctlPbDropdownList.SelectedValue);
scriptBuilder.AppendFormat("&companyId={0}", voPb.CompanyID.Value);
scriptBuilder.Append("') ;");
ScriptManager.RegisterClientScriptBlock(this.Page, this.Page.GetType(), string.Empty, scriptBuilder.ToString(), true);
}
public void GeneratePDF()
{
VOPB voPb = ScgDbQueryProvider.DbPBQuery.GetDescription(UIHelper.ParseLong(Request["pbId"].ToString()), 2);
string pbName = string.Empty;
string companyName = string.Empty;
string pbCode = string.Empty;
if (voPb != null)
{
pbCode = voPb.PBCode;
pbName = voPb.Description;
DbCompany dbCompany = ScgDbQueryProvider.DbCompanyQuery.FindByIdentity(UIHelper.ParseLong(Request["companyId"].ToString()));
companyName = dbCompany.CompanyName;
}
string markList = string.Empty;
string unMarkList = string.Empty;
string maxPaidDate = null;
string minPaidDate = null;
string keyCriteria = ApplicationMode + "reimbursementCriteriaCookie_" + UserAccount.UserID;
string key = ApplicationMode + "reimbursementCookie_" + UserAccount.UserID;
if (Request.Cookies[key] != null)
{
markList = Server.HtmlEncode(Request.Cookies[key]["markList"]);
unMarkList = Server.HtmlEncode(Request.Cookies[key]["unmarkList"]);
}
if (!string.IsNullOrEmpty(Request.Cookies[keyCriteria]["PaidDateFrom"].ToString()))
{
minPaidDate = Server.HtmlEncode(Request.Cookies[keyCriteria]["PaidDateFrom"]);
}
if (!string.IsNullOrEmpty(Request.Cookies[keyCriteria]["PaidDateTo"].ToString()))
{
maxPaidDate = Server.HtmlEncode(Request.Cookies[keyCriteria]["PaidDateTo"]);
}
string filename = string.Format("{0}{1}{2}{3}", pbCode, DateTime.Now.Day.ToString("00"), DateTime.Now.Month.ToString("00"), DateTime.Now.Year);
ReportHelper.FlushReport(this.Page, SCGDocumentService.GenerateReimbursementReport(markList, unMarkList, pbCode, pbName, companyName, UserAccount.UserName, maxPaidDate, minPaidDate), ReportHelper.ReportType.PDF, filename);
}
public ISQLQuery FindPbByCriteria(VOPB pb, string sortExpression, bool isCount)
{
StringBuilder sqlBuilder = new StringBuilder();
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
if (!isCount)
{
sqlBuilder.Append(" select DbPB.PBID as Pbid, DbPB.PBCode as PBCode, DbPB.CompanyCode as CompanyCode, ");
sqlBuilder.Append(" DbPB.Active as Active, DbPB.PettyCashLimit as PettyCashLimit,DbPB.BlockPost as BlockPost , ");
sqlBuilder.Append(" DbPBLang.Description as Description ");
}
else
{
sqlBuilder.Append(" select count(DbPB.PBID) as Count ");
}
sqlBuilder.Append(" from DbPB ");
sqlBuilder.Append(" left join DbPBLang on DbPB.PBID=DbPBLang.PBID and DbPBLang.LanguageID=:langId ");
queryParameterBuilder.AddParameterData("langId", typeof(short), UserAccount.CurrentLanguageID);
#region WhereClause
StringBuilder whereClauseBuilder = new StringBuilder();
if (!string.IsNullOrEmpty(pb.Description))
{
whereClauseBuilder.Append(" and DbPBLang.Description Like :Description ");
queryParameterBuilder.AddParameterData("Description", typeof(string), String.Format("%{0}%", pb.Description));
}
if (!string.IsNullOrEmpty(pb.PBCode))
{
whereClauseBuilder.Append(" and DbPB.PBCode Like :CompanyCode ");
queryParameterBuilder.AddParameterData("CompanyCode", typeof(string), String.Format("%{0}%", pb.PBCode));
}
if (!string.IsNullOrEmpty(whereClauseBuilder.ToString()))
{
sqlBuilder.Append(String.Format(" where 1=1 {0} ", whereClauseBuilder.ToString()));
}
#endregion
#region Order By
if (!isCount)
{
if (!string.IsNullOrEmpty(sortExpression))
{
sqlBuilder.Append(String.Format(" order by {0} ", sortExpression));
}
else
{
sqlBuilder.Append(" order by DbPB.PBCode,DbPB.CompanyCode,DbPBLang.Description,DbPB.PettyCashLimit,DbPB.BlockPost ");
}
}
#endregion
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
queryParameterBuilder.FillParameters(query);
if (isCount)
{
query.AddScalar("Count", NHibernateUtil.Int32);
}
else
{
query.AddScalar("Pbid", NHibernateUtil.Int64)
.AddScalar("PBCode", NHibernateUtil.String)
.AddScalar("Description", NHibernateUtil.String)
.AddScalar("PettyCashLimit", NHibernateUtil.Double)
.AddScalar("BlockPost", NHibernateUtil.Boolean)
.AddScalar("CompanyCode", NHibernateUtil.String)
.AddScalar("Active", NHibernateUtil.Boolean);
query.SetResultTransformer(Transformers.AliasToBean(typeof(VOPB)));
}
return(query);
}
public int CountPbByCriteria(VOPB pb)
{
return(NHibernateQueryHelper.CountByCriteria(ScgDbQueryProvider.DbPBQuery, "FindPbByCriteria", new object[] { pb, string.Empty, true }));
}
public IList <VOPB> GetPbList(VOPB pb, int firstResult, int maxResult, string sortExpression)
{
return(NHibernateQueryHelper.FindPagingByCriteria <VOPB>(ScgDbQueryProvider.DbPBQuery, "FindPbByCriteria", new object[] { pb, sortExpression, false }, firstResult, maxResult, sortExpression));
}
protected object RequestData(int startRow, int pageSize, string sortExpression)
{
VOPB criteria = GetPBCriteria();
return(ScgDbQueryProvider.DbPbRateQuery.GetPbList(criteria, startRow, pageSize, sortExpression));
}
protected void ctlUpdateMarkBtn_Click(object sender, EventArgs e)
{
string key = ApplicationMode + "reimbursementCookie_" + UserAccount.UserID;
Request.Cookies.Remove(key);
HttpCookie reimburstCookie = Request.Cookies[key];
if (reimburstCookie == null)
{
reimburstCookie = new HttpCookie(key);
}
IList <ReimbursementReportValueObj> obj = new List <ReimbursementReportValueObj>();
string markList = string.Empty;
string unMarkList = string.Empty;
string pb = string.Empty;
VOPB voPb = ScgDbQueryProvider.DbPBQuery.GetDescription(Convert.ToInt64(ctlPbDropdownList.SelectedValue), 2);
pb = voPb.PBCode + " " + voPb.Description;
if (Session["MarkStateBeforeUpdate"] != null)
{
IList <ReimbursementReportValueObj> markStateList = (IList <ReimbursementReportValueObj>)Session["MarkStateBeforeUpdate"];
Session.Remove("MarkStateBeforeUpdate");
int rowNo = 0;
foreach (GridViewRow item in ctlReport.Rows)
{
if (item.RowType == DataControlRowType.DataRow)
{
ReimbursementReportValueObj valueObj = new ReimbursementReportValueObj();
HiddenField docNo = (HiddenField)ctlReport.Rows[item.RowIndex].FindControl("ctlDocumentID");
CheckBox mark = (CheckBox)ctlReport.Rows[item.RowIndex].FindControl("ctlMark");
Literal requestNo = (Literal)ctlReport.Rows[item.RowIndex].FindControl("ctlRequestNo");
valueObj.DocumentID = Convert.ToInt64(docNo.Value);
valueObj.Mark = mark.Checked;
if (mark.Checked)
{
if (!markStateList[rowNo].Mark)
{
if (!string.IsNullOrEmpty(markList))
{
markList += ",";
}
markList += requestNo.Text;
}
}
else
{
if (markStateList[rowNo].Mark)
{
if (!string.IsNullOrEmpty(unMarkList))
{
unMarkList += ",";
}
unMarkList += requestNo.Text;
}
}
obj.Add(valueObj);
rowNo++;
}
}
reimburstCookie.Values["markList"] = markList;
reimburstCookie.Values["unmarkList"] = unMarkList;
Response.Cookies.Add(reimburstCookie);
SCGDocumentService.UpdateMarkDocument(obj);
ctlReport.DataCountAndBind();
ctlUpdatePanelMaintainRateGrid.Update();
SetMarkStateBeforeUpdate();
StringBuilder scriptBuilder = new StringBuilder("window.open('ReimbursementReportOutput.aspx?");
scriptBuilder.AppendFormat("pbId={0}", ctlPbDropdownList.SelectedValue);
scriptBuilder.AppendFormat("&companyId={0}", voPb.CompanyID.Value);
scriptBuilder.Append("') ;");
ScriptManager.RegisterClientScriptBlock(this.Page, this.Page.GetType(), string.Empty, scriptBuilder.ToString(), true);
}
}
//public IUserAccount UserAccount { get; set; }
public ISQLQuery FindPbByCriteria(VOPB pb, string sortExpression, bool isCount)
{
StringBuilder sqlBuilder = new StringBuilder();
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
if (!isCount)
{
sqlBuilder.Append(" select distinct DbPB.PBID as Pbid, DbPB.PBCode as PBCode, DbPBLang.Description as Description ");
}
else
{
sqlBuilder.Append(" select count(DbPB.PBID) as Count ");
}
sqlBuilder.Append(" from DbPB ");
sqlBuilder.Append(" left join DbPBLang on DbPB.PBID=DbPBLang.PBID and DbPBLang.LanguageID=:langId ");
sqlBuilder.Append(" inner join SuRolePB on DbPb.PBID=SuRolePB.PBID");
sqlBuilder.Append(" inner join SuUserRole on SuRolePB.RoleID = SuUserRole.RoleID ");
if (pb.UserID.HasValue)
{
sqlBuilder.Append(" and SuUserRole.UserID =:userId ");
queryParameterBuilder.AddParameterData("userId", typeof(long), pb.UserID);
}
queryParameterBuilder.AddParameterData("langId", typeof(short), pb.LanguageID);
#region WhereClause
StringBuilder whereClauseBuilder = new StringBuilder();
if (!string.IsNullOrEmpty(pb.Description))
{
whereClauseBuilder.Append(" and DbPBLang.Description Like :Description ");
queryParameterBuilder.AddParameterData("Description", typeof(string), String.Format("%{0}%", pb.Description));
}
if (!string.IsNullOrEmpty(pb.PBCode))
{
whereClauseBuilder.Append(" and DbPB.PBCode Like :CompanyCode ");
queryParameterBuilder.AddParameterData("CompanyCode", typeof(string), String.Format("%{0}%", pb.PBCode));
}
if (!string.IsNullOrEmpty(whereClauseBuilder.ToString()))
{
sqlBuilder.Append(String.Format(" where 1=1 and DbPB.Active=1 and DbPB.RepOffice=1 {0} ", whereClauseBuilder.ToString()));
}
else
{
sqlBuilder.Append(" where DbPB.Active=1 and DbPB.RepOffice=1 ");
}
#endregion
#region Order By
if (!isCount)
{
if (!string.IsNullOrEmpty(sortExpression))
{
sqlBuilder.Append(String.Format(" order by {0} ", sortExpression));
}
else
{
sqlBuilder.Append("order by DbPB.PBCode,DbPB.PBID,DbPBLang.Description ");
}
}
#endregion
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
queryParameterBuilder.FillParameters(query);
if (isCount)
{
query.AddScalar("Count", NHibernateUtil.Int32);
}
else
{
query.AddScalar("Pbid", NHibernateUtil.Int64)
.AddScalar("PBCode", NHibernateUtil.String)
.AddScalar("Description", NHibernateUtil.String);
query.SetResultTransformer(Transformers.AliasToBean(typeof(VOPB)));
}
return(query);
}
public IList <VOPB> GetPbList(VOPB pb, string sortExpression)
{
return(NHibernateQueryHelper.FindByCriteria <VOPB>(ScgDbQueryProvider.DbPbRateQuery, "FindPbByCriteria", new object[] { pb, sortExpression, false }, sortExpression));
}
