private void Show(bool isPrint = false) { string sql = " 1=1 "; if (txtBusFPNO.Text.Trim() != "" && CommHelp.VerifesToNum_NoString(txtBusFPNO.Text.Trim()) == false) { base.ClientScript.RegisterStartupScript(base.GetType(), null, "<script>alert('公交费发票号码 格式错误!');</script>"); return; } if (txtRepastFPNO.Text.Trim() != "" && CommHelp.VerifesToNum_NoString(txtRepastFPNO.Text.Trim()) == false) { base.ClientScript.RegisterStartupScript(base.GetType(), null, "<script>alert('餐饮发票号码 格式错误!');</script>"); return; } if (txtHotelFPNO.Text.Trim() != "" && CommHelp.VerifesToNum_NoString(txtHotelFPNO.Text.Trim()) == false) { base.ClientScript.RegisterStartupScript(base.GetType(), null, "<script>alert('住宿发票号码 格式错误!');</script>"); return; } if (txtOilFPNO.Text.Trim() != "" && CommHelp.VerifesToNum_NoString(txtOilFPNO.Text.Trim()) == false) { base.ClientScript.RegisterStartupScript(base.GetType(), null, "<script>alert('汽油发票号码 格式错误!');</script>"); return; } if (txtGuoBeginFPNO.Text.Trim() != "" && CommHelp.VerifesToNum_NoString(txtGuoBeginFPNO.Text.Trim()) == false) { base.ClientScript.RegisterStartupScript(base.GetType(), null, "<script>alert('过路费发票号码 格式错误!');</script>"); return; } if (txtPostFPNO.Text.Trim() != "" && CommHelp.VerifesToNum_NoString(txtPostFPNO.Text.Trim()) == false) { base.ClientScript.RegisterStartupScript(base.GetType(), null, "<script>alert('邮寄发票号码 格式错误!');</script>"); return; } if (txtOtherFPNO.Text.Trim() != "" && CommHelp.VerifesToNum_NoString(txtOtherFPNO.Text.Trim()) == false) { base.ClientScript.RegisterStartupScript(base.GetType(), null, "<script>alert('其它费用发票号码 格式错误!');</script>"); return; } if (txtCaiFPNO.Text.Trim() != "" && CommHelp.VerifesToNum_NoString(txtCaiFPNO.Text.Trim()) == false) { base.ClientScript.RegisterStartupScript(base.GetType(), null, "<script>alert('小额采购发票号码 格式错误!');</script>"); return; } if (txtBusFPNO.Text.Trim() != "") { sql += string.Format(" and BusFPNO like '%{0}%'", txtBusFPNO.Text.Trim()); } if (txtRepastFPNO.Text.Trim() != "") { sql += string.Format(" and RepastFPNO like '%{0}%'", txtRepastFPNO.Text.Trim()); } if (txtHotelFPNO.Text.Trim() != "") { sql += string.Format(" and HotelFPNO like '%{0}%'", txtHotelFPNO.Text.Trim()); } if (txtOilFPNO.Text.Trim() != "") { sql += string.Format(" and OilFPNO like '%{0}%'", txtOilFPNO.Text); } if (txtGuoBeginFPNO.Text.Trim() != "") { sql += string.Format(" and GuoBeginFPNO like '%{0}%'", txtGuoBeginFPNO.Text.Trim()); } if (txtPostFPNO.Text.Trim() != "") { sql += string.Format(" and PostFPNO like '%{0}%'", txtPostFPNO.Text.Trim()); } if (txtOtherFPNO.Text.Trim() != "") { sql += string.Format(" and OtherFPNO like '%{0}%'", txtOtherFPNO.Text.Trim()); } if (txtCaiFPNO.Text.Trim() != "") { sql += string.Format(" and CaiFPNO like '%{0}%'", txtCaiFPNO.Text.Trim()); } if (txtFrom.Text != "") { if (CommHelp.VerifesToDateTime(txtFrom.Text) == false) { base.ClientScript.RegisterStartupScript(base.GetType(), null, "<script>alert('日期 格式错误!');</script>"); return; } sql += string.Format(" and Tb_DispatchList.CreateTime>='{0} 00:00:00'", txtFrom.Text); } if (txtTo.Text != "") { if (CommHelp.VerifesToDateTime(txtTo.Text) == false) { base.ClientScript.RegisterStartupScript(base.GetType(), null, "<script>alert('日期 格式错误!');</script>"); return; } sql += string.Format(" and Tb_DispatchList.CreateTime<='{0} 23:59:59'", txtTo.Text); } if (txtGuestName.Text.Trim() != "") { sql += string.Format(" and Tb_DispatchList.GuestName like '%{0}%'", txtGuestName.Text.Trim()); } if (txtPOName.Text.Trim() != "") { sql += string.Format(" and Tb_DispatchList.POName like '%{0}%'", txtPOName.Text.Trim()); } if (txtPONO.Text.Trim() != "") { if (CheckPoNO(txtPONO.Text) == false) { return; } sql += string.Format(" and Tb_DispatchList.PONo like '%{0}%'", txtPONO.Text.Trim()); } if (ddlFuHao.Text != "-1" && !string.IsNullOrEmpty(txtTotal.Text)) { if (CommHelp.VerifesToNum(txtTotal.Text) == false) { base.ClientScript.RegisterStartupScript(base.GetType(), null, "<script>alert('报销金额 格式错误!');</script>"); return; } sql += string.Format(" and ISNULL(BusTotal,0)+ISNULL(RepastTotal,0)+ISNULL(HotelTotal,0)+ISNULL(OilTotal,0)+ISNULL(GuoTotal,0)+ISNULL(PostTotal,0)+ISNULL(Tb_DispatchList.PoTotal,0)+ISNULL(OtherTotal,0) {0}{1}", ddlFuHao.Text, txtTotal.Text); } if (txtProNo.Text.Trim() != "") { if (CheckProNo(txtProNo.Text) == false) { return; } sql += string.Format(" and Tb_DispatchList.CardNo like '%{0}%'", txtProNo.Text.Trim()); } if (ddlFundType.Text != "-1") { if (ddlFundType.Text == "0")//公交费 { sql += string.Format(" and BusTotal is not null and BusTotal>0", ddlFundType.Text); } else if (ddlFundType.Text == "1")//餐饮费 { sql += string.Format(" and RepastTotal is not null and RepastTotal>0", ddlFundType.Text); } else if (ddlFundType.Text == "2")//住宿费 { sql += string.Format(" and HotelTotal is not null and HotelTotal>0", ddlFundType.Text); } else if (ddlFundType.Text == "3")//汽油补贴 { sql += string.Format(" and OilTotal is not null and OilTotal>0", ddlFundType.Text); } else if (ddlFundType.Text == "4")//过路费 { sql += string.Format(" and GuoTotal is not null and GuoTotal>0", ddlFundType.Text); } else if (ddlFundType.Text == "5")//邮寄费 { sql += string.Format(" and PostTotal is not null and PostTotal>0", ddlFundType.Text); } else if (ddlFundType.Text == "6")//小额采购 { sql += string.Format(" and Tb_DispatchList.PoTotal is not null and Tb_DispatchList.PoTotal>0", ddlFundType.Text); } else if (ddlFundType.Text == "7")//其他费用 { sql += string.Format(" and OtherTotal is not null and OtherTotal>0", ddlFundType.Text); } } if (ddlCompany.Text != "-1") { sql += string.Format(" and TB_Company.ComCode='{0}'", ddlCompany.Text.Split(',')[2]); } if (ddlUser.Text != "-1") { sql += string.Format(" and AE='{0}'", ddlUser.SelectedItem.Text); } if (ddlState.Text != "全部") { sql += string.Format(" and Tb_DispatchList.state='{0}'", ddlState.Text); } if (!string.IsNullOrEmpty(txtKeyWords.Text)) { sql += string.Format(@" and ([BusFromAddress] like '%{0}%' or [BusFromAddress] like '%{0}%' or [BusToAddress] like '%{0}%' or CONVERT(varchar(100), BusFromTime, 120) like '%{0}%' or CONVERT(varchar(100), BusToTime, 120) like '%{0}%' or [RepastAddress] like '%{0}%' or [RepastTotal] like '%{0}%' or [RepastPerNum] like '%{0}%' or [RepastPers] like '%{0}%' or [HotelAddress] like '%{0}%' or [HotelName] like '%{0}%' or [OilFromAddress] like '%{0}%' or [OilToAddress] like '%{0}%' or [OilLiCheng] like '%{0}%' or [GuoBeginAddress] like '%{0}%' or [GuoToAddress] like '%{0}%' or [PostFromAddress] like '%{0}%' or [PostToAddress] like '%{0}%' or [PoContext] like '%{0}%' or [OtherContext] like '%{0}%' or [BusRemark] like '%{0}%' or [RepastRemark] like '%{0}%' or [HotelRemark] like '%{0}%' or [OilRemark] like '%{0}%' or [GuoRemark] like '%{0}%' or [PostRemark] like '%{0}%' or Tb_DispatchList.[PoRemark] like '%{0}%' or [OtherRemark] like '%{0}%' or [PostNo] like '%{0}%' or [PostCompany] like '%{0}%' or [PostContext] like '%{0}%' or [PostToPer] like '%{0}%' or [Post_No] like '%{0}%')", txtKeyWords.Text); } if (ddlModel.Text != "全部") { sql += string.Format(" and Model='{0}'", ddlModel.Text); } //增加查询条件 if (ddlGuestTypeList.SelectedValue != "全部") { sql += string.Format(" and GuestType='{0}'", ddlGuestTypeList.SelectedValue); } if (ddlGuestProList.SelectedValue != "-2") { sql += string.Format(" and GuestPro={0}", ddlGuestProList.SelectedValue); } if (ddlClose.Text != "-1") { sql += string.Format(" and CG_POOrder.IsClose={0} ", ddlClose.Text); } if (ddlIsSelect.Text != "-1") { sql += string.Format(" and CG_POOrder.IsSelected={0} ", ddlIsSelect.Text); } if (ddlJieIsSelected.Text != "-1") { sql += string.Format(" and CG_POOrder.JieIsSelected={0} ", ddlJieIsSelected.Text); } if (ddlIsSpecial.Text != "-1") { sql += string.Format(" and CG_POOrder.IsSpecial={0} ", ddlIsSpecial.Text); } if (!string.IsNullOrEmpty(txtPOSTNO.Text.Trim())) { sql += string.Format(" and PostNo LIKE '%{0}%' ", txtPOSTNO.Text.Trim()); } List <Tb_DispatchList> dispatchList = this.dispatchSer.GetListArrayReport(sql); if (isPrint) { Tb_DispatchList sumTotal = new Tb_DispatchList(); sumTotal.Total = dispatchList.Sum(t => t.Total); dispatchList.Add(sumTotal); } lblTotal.Text = dispatchList.Sum(t => t.Total).ToString(); AspNetPager1.RecordCount = dispatchList.Count; this.gvList.PageIndex = AspNetPager1.CurrentPageIndex - 1; this.gvList.DataSource = dispatchList; this.gvList.DataBind(); }