protected void btnSelect_Click(object sender, EventArgs e) { if (CheckProNo(txtProNo.Text) == false) { return; } TB_SupplierAdvancePaymentService mainSer = new TB_SupplierAdvancePaymentService(); List <TB_SupplierAdvancePayment> pp = mainSer.GetListArray(" proNo='" + txtProNo.Text.Trim() + "'"); gvMain.DataSource = pp; gvMain.DataBind(); if (pp.Count > 0) { TB_SupplierAdvancePaymentsService ordersSer = new TB_SupplierAdvancePaymentsService(); List <SupplierToInvoiceView> orders = ordersSer.GetListArray(" 1=1 and TB_SupplierAdvancePayments.id=" + pp[0].Id); gvList.DataSource = orders; gvList.DataBind(); } else { List <SupplierToInvoiceView> orders = new List <SupplierToInvoiceView>(); gvList.DataSource = orders; gvList.DataBind(); } }
protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { TB_SupplierAdvancePaymentService mainSer = new TB_SupplierAdvancePaymentService(); List <TB_SupplierAdvancePayment> pp = new List <TB_SupplierAdvancePayment>(); gvMain.DataSource = pp; gvMain.DataBind(); TB_SupplierAdvancePaymentsService ordersSer = new TB_SupplierAdvancePaymentsService(); List <SupplierToInvoiceView> orders = new List <SupplierToInvoiceView>(); gvList.DataSource = orders; gvList.DataBind(); } }
private void select() { string sql = " 1=1 "; //QueryEForms QuerySession.QueryEForm QEForm = new VAN_OA.QuerySession.QueryEForm(); if (ddlProType.SelectedItem != null && ddlProType.SelectedItem.Text != "") { if (ddlProType.SelectedItem.Value == "-2") { sql += string.Format(" and proId in (26,34)"); } else if (ddlProType.SelectedItem.Value == "-3") { sql += string.Format(" and proId in (31,32)"); } else { sql += string.Format(" and proId={0}", ddlProType.SelectedItem.Value); } QEForm.ProTypeId = Convert.ToInt32(ddlProType.SelectedItem.Value); } else { sql += " and pro_Type is not null"; } if (txtFrom.Text != "") { if (CommHelp.VerifesToDateTime(txtFrom.Text) == false) { base.ClientScript.RegisterStartupScript(base.GetType(), null, "<script>alert('申请时间 格式错误!');</script>"); return; } sql += string.Format(" and appTime>='{0} 00:00:00'", txtFrom.Text); QEForm.FromTime = 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 appTime<='{0} 23:59:59'", txtTo.Text); QEForm.ToTime = txtTo.Text; } if (ddlState.SelectedItem.Text != "") { sql += string.Format(" and state='{0}'", ddlState.SelectedItem.Text); QEForm.State = ddlState.SelectedItem.Text; } if (txtSPForm.Text != "") { if (CommHelp.VerifesToDateTime(txtSPForm.Text) == false) { base.ClientScript.RegisterStartupScript(base.GetType(), null, "<script>alert('审批时间 格式错误!');</script>"); return; } } if (txtSPTo.Text != "") { if (CommHelp.VerifesToDateTime(txtSPTo.Text) == false) { base.ClientScript.RegisterStartupScript(base.GetType(), null, "<script>alert('审批时间 格式错误!');</script>"); return; } } if (txtSPForm.Text != "" && txtSPTo.Text != "") { sql += string.Format(" and isnull(maxDoTime,tb_EForm_View.createTime)>='{0} 00:00:00' and isnull(maxDoTime,tb_EForm_View.createTime)<='{1} 23:59:59' ", txtSPForm.Text, txtSPTo.Text); } if (txtSPForm.Text == "" && txtSPTo.Text != "") { sql += string.Format(" and isnull(maxDoTime,tb_EForm_View.createTime)<='{0} 23:59:59'", txtSPTo.Text); } if (txtSPForm.Text != "" && txtSPTo.Text == "") { sql += string.Format(" and isnull(maxDoTime,tb_EForm_View.createTime)>='{0} 00:00:00' ", txtSPForm.Text); } QEForm.SPForm = txtSPForm.Text; QEForm.SPTo = txtSPTo.Text; if (ddlUser.Text != "-1") { sql += string.Format(" and appPer_Name like '%{0}%'", ddlUser.SelectedItem.Text); QEForm.Apper = ddlUser.SelectedItem.Text; } if (txtAuper.Text != "") { sql += string.Format(" and id in (select e_Id from EForms_View where audPer_Name like '%{0}%' or consignor_Name like '%{0}%')", txtAuper.Text); QEForm.Auper = txtAuper.Text; } if (txtWating.Text != "") { sql += string.Format(" and toPer_Name like '%{0}%'", txtWating.Text); QEForm.WatingAuper = txtWating.Text; } if (txtNo.Text.Trim() != "") { if (CheckProNo(txtNo.Text) == false) { return; } sql += string.Format(" and e_No like '%{0}%'", txtNo.Text.Trim()); QEForm.E_No = txtNo.Text; } if (!string.IsNullOrEmpty(txtPOSupplier.Text.Trim())) { if (cbPiPei.Checked) { sql += string.Format(@" and proId in (31,32,33) and exists(select 1 from (select CAI_POCai.LastSupplier,TB_SupplierAdvancePayments.Id,32 as myProId from TB_SupplierAdvancePayments left join CAI_POCai on TB_SupplierAdvancePayments.CaiIds=CAI_POCai.Ids union all select TB_SupplierInvoice.LastSupplier,TB_SupplierInvoice.Id,31 as myProId from TB_SupplierInvoice left join TB_SupplierInvoices on TB_SupplierInvoice.Id=TB_SupplierInvoices.Id where IsYuFu=0 union all select TB_SupplierInvoice.LastSupplier,TB_SupplierInvoice.Id,33 as myProId from TB_SupplierInvoice left join TB_SupplierInvoices on TB_SupplierInvoice.Id=TB_SupplierInvoices.Id where IsYuFu=1 ) as A where A.myProId=tb_EForm_View.proId and A.Id=tb_EForm_View.allE_id and LastSupplier='{0}' )", txtPOSupplier.Text.Trim()); } else { sql += string.Format(@" and proId in (31,32,33) and exists(select 1 from (select CAI_POCai.LastSupplier,TB_SupplierAdvancePayments.Id,32 as myProId from TB_SupplierAdvancePayments left join CAI_POCai on TB_SupplierAdvancePayments.CaiIds=CAI_POCai.Ids union all select TB_SupplierInvoice.LastSupplier,TB_SupplierInvoice.Id,31 as myProId from TB_SupplierInvoice left join TB_SupplierInvoices on TB_SupplierInvoice.Id=TB_SupplierInvoices.Id where IsYuFu=0 union all select TB_SupplierInvoice.LastSupplier,TB_SupplierInvoice.Id,33 as myProId from TB_SupplierInvoice left join TB_SupplierInvoices on TB_SupplierInvoice.Id=TB_SupplierInvoices.Id where IsYuFu=1 ) as A where A.myProId=tb_EForm_View.proId and A.Id=tb_EForm_View.allE_id and LastSupplier like '%{0}%' )", txtPOSupplier.Text.Trim()); } } #region 项目查询 string ponoSql = ""; if (ddlAEUsers.Text != "-1") { ponoSql += string.Format(" and CG_POOrder.AE='{0}'", ddlAEUsers.SelectedItem.Text); } if (txtPONo.Text.Trim() != "") { if (CheckPoNO(txtPONo.Text) == false) { return; } ponoSql += string.Format(" and CG_POOrder.PONo like '%{0}%'", txtPONo.Text.Trim()); QEForm.PONO = txtPONo.Text; } if (ddlCompany.Text != "-1") { string where = string.Format(" CompanyCode='{0}'", ddlCompany.Text.Split(',')[2]); ponoSql += string.Format(" and CG_POOrder.AE IN(select LOGINNAME from tb_User where {0})", where); } if (txtGuestName.Text.Trim() != "") { ponoSql += string.Format(" and CG_POOrder.GuestName like '%{0}%'", txtGuestName.Text.Trim()); QEForm.GuestName = txtGuestName.Text.Trim(); } if (CheckBox1.Checked) { ponoSql += string.Format(" and CG_POOrder.POStatue='{0}'", CheckBox1.Text); } if (CheckBox2.Checked) { ponoSql += string.Format(" and CG_POOrder.POStatue2='{0}'", CheckBox2.Text); } if (CheckBox3.Checked) { ponoSql += string.Format(" and CG_POOrder.POStatue3='{0}'", CheckBox3.Text); } if (CheckBox4.Checked) { ponoSql += string.Format(" and CG_POOrder.POStatue4='{0}'", CheckBox4.Text); } if (CheckBox5.Checked) { ponoSql += string.Format(" and CG_POOrder.POStatue5='{0}'", CheckBox5.Text); } if (CheckBox6.Checked) { ponoSql += string.Format(" and CG_POOrder.POStatue6='{0}'", CheckBox6.Text); } if (ddlClose.Text != "-1") { ponoSql += string.Format(" and CG_POOrder.IsClose={0} ", ddlClose.Text); } if (ddlIsSelect.Text != "-1") { ponoSql += string.Format(" and CG_POOrder.IsSelected={0} ", ddlIsSelect.Text); } if (ddlJieIsSelected.Text != "-1") { ponoSql += string.Format(" and CG_POOrder.JieIsSelected={0} ", ddlJieIsSelected.Text); } if (ddlIsSpecial.Text != "-1") { ponoSql += string.Format(" and CG_POOrder.IsSpecial={0} ", ddlIsSpecial.Text); } if (ddlHanShui.Text != "-1") { ponoSql += string.Format(" and CG_POOrder.IsPoFax={0} ", ddlHanShui.Text); } if (ddlPrice.Text == "1") { ponoSql += " and Order_ToInvoice_1.POTotal-isnull(TuiTotal,0)>0"; } if (ddlPrice.Text == "0") { ponoSql += " and Order_ToInvoice_1.POTotal-isnull(TuiTotal,0)=0"; } if (ponoSql != "") { sql += string.Format(" and EXISTS (SELECT 1 FROM CG_CAI_POOrder_View as CG_POOrder left join View_AllEform on CG_POOrder.pono=View_AllEform.pono "); if (ddlPrice.Text != "-1") { sql += " left join Order_ToInvoice_1 on CG_POOrder.PONo=Order_ToInvoice_1.PONo "; } sql += string.Format(" WHERE View_AllEform.myProId=tb_EForm_View.proId and View_AllEform.Id=tb_EForm_View.allE_id {0})", ponoSql); } #endregion Session["QueryEForms"] = QEForm; tb_EFormService eformSer = new tb_EFormService(); PagerDomain page = new PagerDomain(); page.CurrentPageIndex = AspNetPager1.CurrentPageIndex; List <tb_EForm> allEForms = eformSer.GetListArray_2_Page(sql, page); AspNetPager1.RecordCount = page.TotalCount; string allIds = "", proIds = "", fukuandan = "0,", yufukuandan = "0,"; for (int i = 0; i < allEForms.Count; i++) { allIds += allEForms[i].allE_id.ToString() + ","; proIds += allEForms[i].proId.ToString() + ","; if (allEForms[i].ProTyleName == "供应商付款单") { fukuandan += allEForms[i].allE_id + ","; } if (allEForms[i].ProTyleName == "供应商预付款单") { yufukuandan += allEForms[i].allE_id + ","; } } allIds = allIds.Trim(','); proIds = proIds.Trim(','); fukuandan = fukuandan.Trim(','); yufukuandan = yufukuandan.Trim(','); if (fukuandan != "" || yufukuandan != "") { var superSer = new TB_SupplierAdvancePaymentService(); SupplierInvoice_Names = superSer.GetSupplierName(yufukuandan, fukuandan); } if (allIds != "") { allAllWform = eformSer.GetView_AllEformList(proIds, allIds); } this.gvList.DataSource = allEForms; this.gvList.DataBind(); }
protected void Page_Load(object sender, EventArgs e) { if (!base.IsPostBack) { TB_CompanyService comSer = new TB_CompanyService(); var comList = comSer.GetListArray(""); foreach (var m in comList) { m.ComSimpName += "," + m.Id + "," + m.ComCode; } comList.Insert(0, new VAN_OA.Model.BaseInfo.TB_Company() { ComSimpName = "-1", ComName = "全部" }); ddlCompany.DataSource = comList; ddlCompany.DataBind(); VAN_OA.Dal.SysUserService userSer = new VAN_OA.Dal.SysUserService(); var aeUsers = userSer.getAllUserByPOList(); aeUsers.Insert(0, new VAN_OA.Model.User { Id = -1, LoginName = "全部" }); ddlAEUsers.DataSource = aeUsers; ddlAEUsers.DataBind(); ddlAEUsers.DataTextField = "LoginName"; ddlAEUsers.DataValueField = "Id"; if (Session["printUrl"] != null) { string url = Session["printUrl"].ToString(); Session["printUrl"] = null; Response.Write(string.Format("<script>window.open('{0}','_blank')</script>", url)); } WebQuerySessin Sess = new WebQuerySessin(Query); A_ProInfoService proSer = new A_ProInfoService(); List <A_ProInfo> pros = this.proSer.GetListArray(""); pros.Insert(0, new A_ProInfo()); ddlProType.DataSource = pros; ddlProType.DataBind(); ddlProType.DataTextField = "pro_Type"; ddlProType.DataValueField = "pro_Id"; //加载SESSION中的数据 if (Session[Query] != null) { //QueryEForms QuerySession.QueryEForm QEForm = Session[Query] as QuerySession.QueryEForm; if (QEForm == null) { return; } if (QEForm.ProTypeId != 0) { try { ddlProType.Text = QEForm.ProTypeId.ToString(); } catch (Exception) { } } if (QEForm.FromTime != "") { txtFrom.Text = QEForm.FromTime; } if (QEForm.ToTime != "") { txtTo.Text = QEForm.ToTime; } if (QEForm.Apper != "") { txtApper.Text = QEForm.Apper; } if (QEForm.WeiTuo != "") { txtWeiTuo.Text = QEForm.WeiTuo; } if (QEForm.E_No != "") { txtNo.Text = QEForm.E_No; } if (QEForm.E_No != "") { txtNo.Text = QEForm.E_No; } select(); } else { tb_EFormService eformSer = new tb_EFormService(); List <tb_EForm> allEForms = eformSer.GetListArray_ToDo("", "", Convert.ToInt32(Session["currentUserId"])); string allIds = "", proIds = "", fukuandan = "0,", yufukuandan = "0,"; for (int i = 0; i < allEForms.Count; i++) { allIds += allEForms[i].allE_id.ToString() + ","; proIds += allEForms[i].proId.ToString() + ","; if (allEForms[i].ProTyleName == "供应商付款单") { fukuandan += allEForms[i].allE_id + ","; } if (allEForms[i].ProTyleName == "供应商预付款单") { yufukuandan += allEForms[i].allE_id + ","; } } allIds = allIds.Trim(','); proIds = proIds.Trim(','); fukuandan = fukuandan.Trim(','); yufukuandan = yufukuandan.Trim(','); if (fukuandan != "" || yufukuandan != "") { var superSer = new TB_SupplierAdvancePaymentService(); SupplierInvoice_Names = superSer.GetSupplierName(yufukuandan, fukuandan); } if (allIds != "") { allAllWform = eformSer.GetView_AllEformList(proIds, allIds); } AspNetPager1.RecordCount = allEForms.Count; this.gvList.PageIndex = AspNetPager1.CurrentPageIndex - 1; this.gvList.DataSource = allEForms; this.gvList.DataBind(); } } }
private void select() { if (txtFrom.Text != "") { if (CommHelp.VerifesToDateTime(txtFrom.Text) == false) { base.ClientScript.RegisterStartupScript(base.GetType(), null, "<script>alert('申请时间 格式错误!');</script>"); return; } } if (txtTo.Text != "") { if (CommHelp.VerifesToDateTime(txtTo.Text) == false) { base.ClientScript.RegisterStartupScript(base.GetType(), null, "<script>alert('申请时间 格式错误!');</script>"); return; } } if (txtSPForm.Text != "") { if (CommHelp.VerifesToDateTime(txtSPForm.Text) == false) { base.ClientScript.RegisterStartupScript(base.GetType(), null, "<script>alert('审批时间 格式错误!');</script>"); return; } } if (txtSPTo.Text != "") { if (CommHelp.VerifesToDateTime(txtSPTo.Text) == false) { base.ClientScript.RegisterStartupScript(base.GetType(), null, "<script>alert('审批时间 格式错误!');</script>"); return; } } string sql = " 1=1 "; //QueryEForms QuerySession.QueryEForm QEForm = new VAN_OA.QuerySession.QueryEForm(); if (txtSPForm.Text != "" && txtSPTo.Text != "") { sql += string.Format(" and isnull(maxDoTime,tb_EForm_View.createTime)>='{0} 00:00:00' and isnull(maxDoTime,tb_EForm_View.createTime)<='{1} 23:59:59' ", txtSPForm.Text, txtSPTo.Text); } if (txtSPForm.Text == "" && txtSPTo.Text != "") { sql += string.Format(" and isnull(maxDoTime,tb_EForm_View.createTime)<='{0} 23:59:59'", txtSPTo.Text); } if (txtSPForm.Text != "" && txtSPTo.Text == "") { sql += string.Format(" and isnull(maxDoTime,tb_EForm_View.createTime)>='{0} 00:00:00' ", txtSPForm.Text); } QEForm.SPForm = txtSPForm.Text; QEForm.SPTo = txtSPTo.Text; if (ddlProType.SelectedItem != null && ddlProType.SelectedItem.Text != "") { sql += string.Format(" and proId={0}", ddlProType.SelectedItem.Value); QEForm.ProTypeId = Convert.ToInt32(ddlProType.SelectedItem.Value); } if (txtFrom.Text != "") { sql += string.Format(" and appTime>='{0} 00:00:00'", txtFrom.Text); QEForm.FromTime = txtFrom.Text; } if (txtTo.Text != "") { sql += string.Format(" and appTime<='{0} 23:59:59'", txtTo.Text); QEForm.ToTime = txtTo.Text; } if (ddlState.SelectedItem.Text != "") { sql += string.Format(" and state='{0}'", ddlState.SelectedItem.Text); QEForm.State = ddlState.SelectedItem.Text; } if (txtNo.Text.Trim() != "") { if (CheckProNo(txtNo.Text) == false) { return; } sql += string.Format(" and e_No like '%{0}%'", txtNo.Text.Trim()); QEForm.E_No = txtNo.Text.Trim(); } //sql += string.Format(" and appPer={0}",Session["currentUserId"].ToString()); string SuccessCai = string.Format(@"select id from tb_EForm where tb_EForm.alle_id in (select id from TB_POOrder where caigou='{0}') and proid=(select pro_Id from A_ProInfo where pro_Type='订单报批表')", base.Session["LoginName"].ToString()); sql += string.Format(" and (appPer={0} or id in ({1}))", Session["currentUserId"].ToString(), SuccessCai); if (Request["Type"] != null) { string type = ""; if (Request["Type"] != null) { type = Request["Type"].ToString(); } if (type == "Success") { string Success = string.Format(@"select id from tb_EForm left join (select e_id,Max(doTime)AS MaxTime from tb_EForms group by e_id) as newTable on tb_EForm.id=newTable.e_id where state='通过' and appPer={0} and MaxTime between '{1} 00:00:00' and '{1} 23:59:59'", Session["currentUserId"], DateTime.Now.ToShortDateString()); sql += string.Format(@" and id in ({0})", Success); } if (type == "SuccessCai") { // string Success = string.Format(@"select id from tb_EForm left join (select e_id,Max(doTime)AS MaxTime from tb_EForms group by e_id) as newTable on tb_EForm.id=newTable.e_id // where state='通过' and appPer={0} and MaxTime between '{1} 00:00:00' and '{1} 23:59:59'", Session["currentUserId"], DateTime.Now.ToShortDateString()); string Success = string.Format(@"select id from tb_EForm left join (select e_id,Max(doTime)AS MaxTime from tb_EForms group by e_id) as newTable on tb_EForm.id=newTable.e_id where state='通过' and MaxTime between '{1} 00:00:00' and '{1} 23:59:59' and tb_EForm.alle_id in (select id from TB_POOrder where caigou='{0}') and proid=(select pro_Id from A_ProInfo where pro_Type='订单报批表')", base.Session["LoginName"].ToString(), DateTime.Now.ToShortDateString()); sql += string.Format(@" and id in ({0})", Success); } if (type == "Fail") { string Fail = string.Format(@"select id from tb_EForm left join (select e_id,Max(doTime)AS MaxTime from tb_EForms group by e_id) as newTable on tb_EForm.id=newTable.e_id where state='不通过' and appPer={0} and MaxTime between '{1} 00:00:00' and '{1} 23:59:59'", Session["currentUserId"], DateTime.Now.ToShortDateString()); sql += string.Format(@" and id in ({0})", Fail); } } #region 项目查询 string ponoSql = ""; if (txtPONo.Text.Trim() != "") { if (CheckPoNO(txtPONo.Text.Trim()) == false) { return; } ponoSql += string.Format(" and CG_POOrder.PONo like '%{0}%'", txtPONo.Text.Trim()); QEForm.PONO = txtPONo.Text.Trim(); } if (ddlCompany.Text != "-1") { string where = string.Format(" CompanyCode='{0}'", ddlCompany.Text.Split(',')[2]); ponoSql += string.Format(" and CG_POOrder.AE IN(select LOGINNAME from tb_User where {0})", where); } if (ponoSql != "") { sql += string.Format(" and EXISTS (SELECT 1 FROM CG_CAI_POOrder_View as CG_POOrder left join View_AllEform on CG_POOrder.pono=View_AllEform.pono "); sql += string.Format(" WHERE View_AllEform.myProId=tb_EForm_View.proId and View_AllEform.Id=tb_EForm_View.allE_id {0})", ponoSql); } #endregion tb_EFormService eformSer = new tb_EFormService(); List <tb_EForm> allEForms; //if (AspNetPager1.CurrentPageIndex == 1) //{ allEForms = eformSer.GetListArray_1(sql); // ViewState["myList"] = allEForms; //} //else //{ // allEForms=ViewState["myList"] as List<tb_EForm>; //} Session["QueryRequestEForms"] = QEForm; AspNetPager1.RecordCount = allEForms.Count; this.gvList.PageIndex = AspNetPager1.CurrentPageIndex - 1; string allIds = "", proIds = "", fukuandan = "0,", yufukuandan = "0,"; for (int i = gvList.PageIndex * 10; i < (gvList.PageIndex + 1) * 10; i++) { if (i >= allEForms.Count) { break; } allIds += allEForms[i].allE_id.ToString() + ","; proIds += allEForms[i].proId.ToString() + ","; if (allEForms[i].ProTyleName == "供应商付款单") { fukuandan += allEForms[i].allE_id + ","; } if (allEForms[i].ProTyleName == "供应商预付款单") { yufukuandan += allEForms[i].allE_id + ","; } } allIds = allIds.Trim(','); proIds = proIds.Trim(','); fukuandan = fukuandan.Trim(','); yufukuandan = yufukuandan.Trim(','); if (fukuandan != "" || yufukuandan != "") { var superSer = new TB_SupplierAdvancePaymentService(); SupplierInvoice_Names = superSer.GetSupplierName(yufukuandan, fukuandan); } if (allIds != "") { allAllWform = eformSer.GetView_AllEformList(proIds, allIds); } this.gvList.DataSource = allEForms; this.gvList.DataBind(); }
private void select() { WarnDays = new Base_WarnDaysService().GetListArray(""); string sql = " 1=1 "; //QueryEForms QuerySession.QueryEForm QEForm = new VAN_OA.QuerySession.QueryEForm(); if (txtFrom.Text != "") { if (CommHelp.VerifesToDateTime(txtFrom.Text) == false) { base.ClientScript.RegisterStartupScript(base.GetType(), null, "<script>alert('申请时间 格式错误!');</script>"); return; } } if (txtTo.Text != "") { if (CommHelp.VerifesToDateTime(txtTo.Text) == false) { base.ClientScript.RegisterStartupScript(base.GetType(), null, "<script>alert('申请时间 格式错误!');</script>"); return; } } if (txtSPForm.Text != "") { if (CommHelp.VerifesToDateTime(txtSPForm.Text) == false) { base.ClientScript.RegisterStartupScript(base.GetType(), null, "<script>alert('审批时间 格式错误!');</script>"); return; } } if (txtSPTo.Text != "") { if (CommHelp.VerifesToDateTime(txtSPTo.Text) == false) { base.ClientScript.RegisterStartupScript(base.GetType(), null, "<script>alert('审批时间 格式错误!');</script>"); return; } } if (txtSPForm.Text != "" && txtSPTo.Text != "") { sql += string.Format(" and isnull(e_LastTime,table2.createTime)>='{0} 00:00:00' and isnull(maxDoTime,table2.createTime)<='{1} 23:59:59' ", txtSPForm.Text, txtSPTo.Text); } if (txtSPForm.Text == "" && txtSPTo.Text != "") { sql += string.Format(" and isnull(e_LastTime,table2.createTime)<='{0} 23:59:59'", txtSPTo.Text); } if (txtSPForm.Text != "" && txtSPTo.Text == "") { sql += string.Format(" and isnull(e_LastTime,table2.createTime)>='{0} 00:00:00' ", txtSPForm.Text); } QEForm.SPForm = txtSPForm.Text; QEForm.SPTo = txtSPTo.Text; if (ddlProType.SelectedItem != null && ddlProType.SelectedItem.Text != "") { sql += string.Format(" and proId={0}", ddlProType.SelectedItem.Value); QEForm.ProTypeId = Convert.ToInt32(ddlProType.SelectedItem.Value); } if (txtFrom.Text != "") { sql += string.Format(" and appTime>='{0} 00:00:00'", txtFrom.Text); QEForm.FromTime = txtFrom.Text; } if (txtTo.Text != "") { sql += string.Format(" and appTime<='{0} 23:59:59'", txtTo.Text); QEForm.ToTime = txtTo.Text; } if (txtApper.Text != "") { sql += string.Format(" and AppPer_Name like '%{0}%'", txtApper.Text); QEForm.Apper = txtApper.Text; } //sql += string.Format(" and toPer='{0}'", Session["LoginName"].ToString()); if (txtWeiTuo.Text != "") { sql += string.Format(" and (toPer like '%{0}%' and type1='委托')", txtWeiTuo.Text); QEForm.WeiTuo = txtWeiTuo.Text; } sql += string.Format(" and state='执行中'"); if (txtNo.Text.Trim() != "") { if (CheckProNo(txtNo.Text) == false) { return; } sql += string.Format(" and e_No like '%{0}%'", txtNo.Text.Trim()); QEForm.E_No = txtNo.Text.Trim(); } //if (txtNo.Text != "") //{ // if (CheckProNo(txtNo.Text) == false) // { // return; // } // sql += string.Format(" and e_No like '%{0}%'", txtNo.Text); // QEForm.E_No = txtNo.Text; //} if (!string.IsNullOrEmpty(txtPOSupplier.Text.Trim())) { if (cbPiPei.Checked) { sql += string.Format(@" and proId in (31,32,33) and exists(select 1 from (select CAI_POCai.LastSupplier,TB_SupplierAdvancePayments.Id,32 as myProId from TB_SupplierAdvancePayments left join CAI_POCai on TB_SupplierAdvancePayments.CaiIds=CAI_POCai.Ids union all select TB_SupplierInvoice.LastSupplier,TB_SupplierInvoice.Id,31 as myProId from TB_SupplierInvoice left join TB_SupplierInvoices on TB_SupplierInvoice.Id=TB_SupplierInvoices.Id where IsYuFu=0 union all select TB_SupplierInvoice.LastSupplier,TB_SupplierInvoice.Id,33 as myProId from TB_SupplierInvoice left join TB_SupplierInvoices on TB_SupplierInvoice.Id=TB_SupplierInvoices.Id where IsYuFu=1 ) as A where A.myProId=table2.proId and A.Id=table2.allE_id and LastSupplier='{0}' )", txtPOSupplier.Text.Trim()); } else { sql += string.Format(@" and proId in (31,32,33) and exists(select 1 from (select CAI_POCai.LastSupplier,TB_SupplierAdvancePayments.Id,32 as myProId from TB_SupplierAdvancePayments left join CAI_POCai on TB_SupplierAdvancePayments.CaiIds=CAI_POCai.Ids union all select TB_SupplierInvoice.LastSupplier,TB_SupplierInvoice.Id,31 as myProId from TB_SupplierInvoice left join TB_SupplierInvoices on TB_SupplierInvoice.Id=TB_SupplierInvoices.Id where IsYuFu=0 union all select TB_SupplierInvoice.LastSupplier,TB_SupplierInvoice.Id,33 as myProId from TB_SupplierInvoice left join TB_SupplierInvoices on TB_SupplierInvoice.Id=TB_SupplierInvoices.Id where IsYuFu=1 ) as A where A.myProId=table2.proId and A.Id=table2.allE_id and LastSupplier like '%{0}%' )", txtPOSupplier.Text.Trim()); } } #region 项目查询 string ponoSql = ""; if (txtPONo.Text.Trim() != "") { if (CheckPoNO(txtPONo.Text.Trim()) == false) { return; } ponoSql += string.Format(" and CG_POOrder.PONo like '%{0}%'", txtPONo.Text.Trim()); QEForm.PONO = txtPONo.Text; } if (ddlAEUsers.Text != "-1") { ponoSql += string.Format(" and CG_POOrder.AE='{0}'", ddlAEUsers.SelectedItem.Text); } if (ddlCompany.Text != "-1") { string where = string.Format(" CompanyCode='{0}'", ddlCompany.Text.Split(',')[2]); ponoSql += string.Format(" and CG_POOrder.AE IN(select LOGINNAME from tb_User where {0})", where); } if (ddlClose.Text != "-1") { ponoSql += string.Format(" and CG_POOrder.IsClose={0} ", ddlClose.Text); } if (ddlIsSelect.Text != "-1") { ponoSql += string.Format(" and CG_POOrder.IsSelected={0} ", ddlIsSelect.Text); } if (ddlJieIsSelected.Text != "-1") { ponoSql += string.Format(" and CG_POOrder.JieIsSelected={0} ", ddlJieIsSelected.Text); } if (ddlIsSpecial.Text != "-1") { ponoSql += string.Format(" and CG_POOrder.IsSpecial={0} ", ddlIsSpecial.Text); } if (ddlHanShui.Text != "-1") { ponoSql += string.Format(" and CG_POOrder.IsPoFax={0} ", ddlHanShui.Text); } if (ddlPrice.Text == "1") { ponoSql += " and Order_ToInvoice_1.POTotal-isnull(TuiTotal,0)>0"; } if (ddlPrice.Text == "0") { ponoSql += " and Order_ToInvoice_1.POTotal-isnull(TuiTotal,0)=0"; } string poSQL = ""; if (ponoSql != "") { poSQL += string.Format(" and EXISTS (SELECT 1 FROM CG_CAI_POOrder_View as CG_POOrder left join View_AllEform on CG_POOrder.pono=View_AllEform.pono "); if (ddlPrice.Text != "-1") { poSQL += " left join Order_ToInvoice_1 on CG_POOrder.PONo=Order_ToInvoice_1.PONo "; } poSQL += string.Format(" WHERE View_AllEform.myProId=tb_EForm_View.proId and View_AllEform.Id=tb_EForm_View.allE_id {0})", ponoSql); } #endregion tb_EFormService eformSer = new tb_EFormService(); List <tb_EForm> allEForms = eformSer.GetListArray_ToDo(sql, poSQL, Convert.ToInt32(Session["currentUserId"])); string allIds = "", proIds = "", fukuandan = "0,", yufukuandan = "0,"; for (int i = 0; i < allEForms.Count; i++) { allIds += allEForms[i].allE_id.ToString() + ","; proIds += allEForms[i].proId.ToString() + ","; if (allEForms[i].ProTyleName == "供应商付款单") { fukuandan += allEForms[i].allE_id + ","; } if (allEForms[i].ProTyleName == "供应商预付款单") { yufukuandan += allEForms[i].allE_id + ","; } } allIds = allIds.Trim(','); proIds = proIds.Trim(','); fukuandan = fukuandan.Trim(','); yufukuandan = yufukuandan.Trim(','); if (fukuandan != "" || yufukuandan != "") { var superSer = new TB_SupplierAdvancePaymentService(); SupplierInvoice_Names = superSer.GetSupplierName(yufukuandan, fukuandan); } if (allIds != "") { allAllWform = eformSer.GetView_AllEformList(proIds, allIds); } //proId 进行分类管理 // 1:常用,(就是除 项目订单、采购订单、预付款转支付单、销售发票修改、销售发票删除、用车明细表之外的所有单据),查询条件按原来 + 上述增加的一行条件。 var changyong_allEForms = allEForms.FindAll(t => t.proId != 19 && t.proId != 20 && t.proId != 26 && t.proId != 33 && t.proId != 34 && t.proId != 37 && t.proId != 5); //2:项目订单及采购,包含项目订单和采购订单,查询条件,查询条件按原来 + 上述增加的一行条件。。 var pono_Cai_allEForms = allEForms.FindAll(t => t.proId == 19 || t.proId == 20); //3:预付款转支付单,如图3,查询条件按原来 + 上述增加的一行条件。。 var yuZhuan_allEForms = allEForms.FindAll(t => t.proId == 33); //4:销售发票,包含销售发票,销售发票修改、销售发票删除,查询条件按原来 + 上述增加的一行条件。。 var xiaoShou_allEForms = allEForms.FindAll(t => t.proId == 26 || t.proId == 34 || t.proId == 37); //5:用车明细表,查询条件按原来 + 上述增加的一行条件。 var yongChe_allEForms = allEForms.FindAll(t => t.proId == 5); Session[Query] = QEForm; this.gvList.DataSource = changyong_allEForms; this.gvList.DataBind(); this.GridView2.DataSource = pono_Cai_allEForms; this.GridView2.DataBind(); this.GridView3.DataSource = yuZhuan_allEForms; this.GridView3.DataBind(); this.GridView4.DataSource = xiaoShou_allEForms; this.GridView4.DataBind(); this.GridView5.DataSource = yongChe_allEForms; this.GridView5.DataBind(); }
private void select() { string sql = " 1=1 "; //QueryEForms QuerySession.QueryEForm QEForm = new VAN_OA.QuerySession.QueryEForm(); if (ddlProType.SelectedItem!=null&& ddlProType.SelectedItem.Text != "") { sql += string.Format(" and proId={0}", ddlProType.SelectedItem.Value); QEForm.ProTypeId = Convert.ToInt32(ddlProType.SelectedItem.Value); } if (txtFrom.Text != "") { if (CommHelp.VerifesToDateTime(txtFrom.Text.Trim()) == false) { base.ClientScript.RegisterStartupScript(base.GetType(), null, "<script>alert('审批时间 格式错误!');</script>"); return; } sql += string.Format(" and DoTime>='{0} 00:00:00'", txtFrom.Text); QEForm.FromTime = txtFrom.Text; } if (txtTo.Text != "") { if (CommHelp.VerifesToDateTime(txtTo.Text.Trim()) == false) { base.ClientScript.RegisterStartupScript(base.GetType(), null, "<script>alert('审批时间 格式错误!');</script>"); return; } sql += string.Format(" and DoTime<='{0} 23:59:59'", txtTo.Text); QEForm.ToTime = txtTo.Text; } if (ddlState.SelectedItem.Text != "") { sql += string.Format(" and state='{0}'", ddlState.SelectedItem.Text); QEForm.State = ddlState.Text; } if (txtApper.Text != "") { sql += string.Format(" and appPer_Name like '%{0}%'", txtApper.Text); QEForm.Apper = txtApper.Text; } if (txtNo.Text.Trim() != "") { if (CheckProNo(txtNo.Text) == false) { return; } sql += string.Format(" and e_No like '%{0}%'", txtNo.Text.Trim()); QEForm.E_No = txtNo.Text; } #region 项目查询 string ponoSql = ""; if (ddlCompany.Text != "-1") { string where = string.Format(" CompanyCode='{0}'", ddlCompany.Text.Split(',')[2]); ponoSql += string.Format(" and CG_POOrder.AE IN(select LOGINNAME from tb_User where {0})", where); } if (ddlAEUsers.Text != "-1") { ponoSql += string.Format(" and CG_POOrder.AE='{0}'", ddlAEUsers.SelectedItem.Text); } if (ponoSql != "") { sql += string.Format(" and EXISTS (SELECT 1 FROM CG_CAI_POOrder_View as CG_POOrder left join View_AllEform on CG_POOrder.pono=View_AllEform.pono "); sql += string.Format(" WHERE View_AllEform.myProId=tb_EForm_View.proId and View_AllEform.Id=tb_EForm_View.allE_id {0})", ponoSql); } #endregion tb_EFormService eformSer = new tb_EFormService(); List<tb_EForm> allEForms; //if (AspNetPager1.CurrentPageIndex == 1) //{ allEForms = eformSer.GetListArray_MyApps(sql, Convert.ToInt32(Session["currentUserId"])); // ViewState["myList"] = allEForms; //} //else //{ // allEForms=ViewState["myList"] as List<tb_EForm>; //} Session[Query] = QEForm; AspNetPager1.RecordCount = allEForms.Count; this.gvList.PageIndex = AspNetPager1.CurrentPageIndex - 1; string allIds = "", proIds = "", fukuandan = "0,", yufukuandan = "0,"; for (int i = gvList.PageIndex*10; i < (gvList.PageIndex+1)*10; i++) { if (i >= allEForms.Count) { break; } allIds += allEForms[i].allE_id.ToString() + ","; proIds += allEForms[i].proId.ToString() + ","; if (allEForms[i].ProTyleName == "供应商付款单") { fukuandan += allEForms[i].allE_id + ","; } if (allEForms[i].ProTyleName == "供应商预付款单") { yufukuandan += allEForms[i].allE_id + ","; } } allIds = allIds.Trim(','); proIds = proIds.Trim(','); fukuandan = fukuandan.Trim(','); yufukuandan = yufukuandan.Trim(','); if (fukuandan != "" || yufukuandan != "") { var superSer = new TB_SupplierAdvancePaymentService(); SupplierInvoice_Names = superSer.GetSupplierName(yufukuandan, fukuandan); } if (allIds != "") { allAllWform = eformSer.GetView_AllEformList(proIds, allIds); } this.gvList.DataSource = allEForms; this.gvList.DataBind(); }