private void Show() { string sql = ""; QuerySession.QueryGuestTrack QGuestTrack = new VAN_OA.QuerySession.QueryGuestTrack(); if (txtGuestName.Text.Trim() != "") { sql += string.Format(" and GuestName like '%{0}%'", txtGuestName.Text.Trim()); QGuestTrack.GuestName = txtGuestName.Text.Trim(); } // sql += string.Format(@" and (TB_GuestTrack.id in (select allE_id from tb_EForm where proId =17 and state='通过') //or TB_GuestTrack.id not in (select allE_id from tb_EForm where proId =17))"); PagerDomain page = new PagerDomain(); page.CurrentPageIndex = AspNetPager1.CurrentPageIndex; List <TB_GuestTrack> GuestTracks = this.GuestTrackSer.GetListArrayToPage(sql, page).OrderByDescending(t => t.Time).ToList(); AspNetPager1.RecordCount = GuestTracks.Count; this.gvList.PageIndex = AspNetPager1.CurrentPageIndex - 1; Session[Query] = QGuestTrack; this.gvList.DataSource = GuestTracks; this.gvList.DataBind(); }
public string GetPagerSql(string sqlBody, QueryCondition query, IDbParameters parameters, string otherWhere = "", string otherSum = "") { PagerDomain domain = query.GetPager(); string strWhere = query.GetSQL_Where(parameters).ToString(); string orderby = query.GetSQL_Order().ToString(); #region 查询总条数 var strSql = new StringBuilder(); strSql.Append("select count(1) "); if (!string.IsNullOrEmpty(otherSum)) { strSql.Append("," + otherSum); } strSql.Append(sqlBody.Substring(sqlBody.ToUpper().IndexOf(" FROM "))); if (!string.IsNullOrEmpty(strWhere.Trim())) { strSql.Append(strWhere); } if (!string.IsNullOrEmpty(otherWhere.Trim())) { strSql.Append(otherWhere); } var allCount = DataSet_ReadAdoTemplate.DataSetCreateWithParams(CommandType.Text, strSql.ToString(), parameters); domain.TotalCount = Convert.ToInt32(allCount.Tables[0].Rows[0][0]); domain.SumDT = allCount.Tables[0]; #endregion #region 拼接好的分页SQL strSql = new StringBuilder(); strSql.Append("SELECT * FROM ( "); strSql.Append(" SELECT ROW_NUMBER() OVER ("); if (!string.IsNullOrEmpty(orderby.Trim())) { strSql.Append(orderby); } strSql.AppendFormat(")AS Row, {0} ", sqlBody.Substring(sqlBody.ToUpper().IndexOf("SELECT ") + 7)); if (!string.IsNullOrEmpty(strWhere.Trim())) { strSql.Append(strWhere); } if (!string.IsNullOrEmpty(otherWhere.Trim())) { strSql.Append(otherWhere); } strSql.Append(" ) TT"); strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", ((domain.CurrentPage - 1) * domain.PageSize + 1), domain.CurrentPage * domain.PageSize); #endregion return(strSql.ToString()); }
private void Show() { string sql = " 1=1 "; PagerDomain page = new PagerDomain(); page.PageSize = 50; page.CurrentPageIndex = AspNetPager1.CurrentPageIndex; List <MyExcel> invoiceList = this.myExcelSer.GetSonExcel(Request["Excel"], sql, page); AspNetPager1.RecordCount = page.TotalCount; this.gvList.DataSource = invoiceList; this.gvList.DataBind(); }
private void select() { PagerDomain page = new PagerDomain(); page.CurrentPageIndex = AspNetPager1.CurrentPageIndex; if (ddlYear.SelectedValue == "" || ddlMonth.SelectedValue == "" || Chk_All.Checked) { this.gvList.DataSource = ApprovePAFormListSer.GetAllPAFormList(ddlUser.SelectedValue, "", page); } else { this.gvList.DataSource = ApprovePAFormListSer.GetAllPAFormList(ddlUser.SelectedValue, ddlYear.SelectedValue + "-" + ddlMonth.SelectedValue, page); } AspNetPager1.RecordCount = page.TotalCount; this.gvList.DataBind(); }
/// <summary> /// 得到一个对象实体 /// </summary> public DataTable GetAllPAFormList(string UserID, string Month, PagerDomain page) { string sql = "select P.PAFormID,U.loginName,U.zhiwu,P.Month,P.AttendDays,P.LeaveDays,P.FullAttendBonus,dbo.getPAStatus(P.Status) as Status,dbo.getPAFirstScoreSum(P.PAFormID) as PAFirstScoreSum,dbo.getPASecondScoreSum(P.PAFormID) as PASecondScoreSum,dbo.getPAMultiScoreSum(P.PAFormID) as PAMultiScoreSum,dbo.getPASumAVG(P.PAFormID) as PASumAVG,dbo.getPAAmountSum(P.PAFormID) as PAAmountSum,dbo.getPASubNotes(P.PAFormID) as Note2,Convert(bit,0) as IsDeleted FROM tb_UserMonthPAFormHead P left join tb_User U on P.UserID=U.ID "; string strWhere = " P.PAFormID is not null"; if (UserID != "") { strWhere += " and P.PAFormID in (Select PAFormID from tb_UserMonthPAForm where UserID=" + UserID + ")"; } if (Month != "") { strWhere += " and Month>='" + Month + "'"; } var strSql = new StringBuilder(DBHelp.GetPagerSql(page, sql, strWhere, " U.loginName,P.Month ")); return(DBHelp.getDataTable(strSql.ToString())); }
private void Show() { string sql = " 1=1 "; if (txtContent.Text != "") { sql += string.Format(" and ExcelJosnContent like '%{0}%'", txtContent.Text); } PagerDomain page = new PagerDomain(); page.PageSize = 50; page.CurrentPageIndex = AspNetPager1.CurrentPageIndex; List <MyExcel> invoiceList = this.myExcelSer.GetSonExcel(ddlExcel.Text, sql, page); AspNetPager1.RecordCount = page.TotalCount; //this.gvList.PageIndex = AspNetPager1.CurrentPageIndex - 1; this.gvList.DataSource = invoiceList; this.gvList.DataBind(); }
public List <MyExcel> GetSonExcel_PiPei(string table, string strWhere, PagerDomain page) { List <MyExcel> excels = new List <MyExcel>(); StringBuilder strSql = new StringBuilder(); strSql.Append("select Ids "); strSql.Append(" FROM [ExcelData].[dbo].[" + table + "] "); //if (strWhere.Trim() != "") //{ // strSql.Append(" where " + strWhere); //} strSql = new StringBuilder(DBHelp.GetPagerSql(page, strSql.ToString(), strWhere, " Ids ")); using (SqlConnection conn = DBHelp.getConn()) { conn.Open(); SqlCommand objCommand = new SqlCommand(strSql.ToString(), conn); objCommand.CommandTimeout = 120; using (SqlDataReader objReader = objCommand.ExecuteReader()) { while (objReader.Read()) { MyExcel model = new MyExcel(); object ojb; ojb = objReader["Ids"]; if (ojb != null && ojb != DBNull.Value) { model.Id = (int)ojb; } excels.Add(model); } } } return(excels); }
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(); }
public List <VAN_OA.Model.ReportForms.vAllCaiOrderList> GetListArrayAll_Page(string strWhere, PagerDomain page, out decimal Total) { Total = 0; StringBuilder strSql = new StringBuilder(); strSql.Append("select sum(Num*lastPrice) as total "); strSql.Append(" FROM vAllCaiOrderList left join tb_EForm on tb_EForm.allE_id=vAllCaiOrderList.id and proId=20 "); if (strWhere.Trim() != "") { strSql.Append(" where " + strWhere); } var total = DBHelp.ExeScalar(strSql.ToString()); if (total != null && total != DBNull.Value) { Total = (decimal)total; } strSql = new StringBuilder(); strSql.Append("select LastTruePrice,TruePrice1,TruePrice2,TruePrice3,CaiFpType,vAllCaiOrderList.Id,AppName,CaiGou,cRemark,ProNo,PONo,BusType,POName,PODate,POPayStype,POTotal,GuestName,AE,INSIDE,GuestNo,Status,CG_ProNo,Time,InvName,Num,Unit,CostPrice,SellPrice,OtherCost,ToTime,Profit,GoodId,CG_POOrdersId,CaiTime,Supplier,SupperPrice,UpdateUser,Idea,Supplier1,SupperPrice1,Supplier2,SupperPrice2,FinPrice1,FinPrice2,FinPrice3,cbifDefault1,cbifDefault2,cbifDefault3,lastSupplier,lastPrice,GoodNo,GoodName,GoodSpec,GoodModel,GoodUnit,GoodTypeSmName,IsHanShui,ids"); strSql.Append(" FROM vAllCaiOrderList "); //strSql.Append(" left join tb_EForm on tb_EForm.allE_id=vAllCaiOrderList.id and proId=20 "); //if (strWhere.Trim() != "") //{ // strSql.Append(" where " + strWhere); //} //strSql.Append(" order by id desc"); strSql = new StringBuilder(DBHelp.GetPagerSql(page, strSql.ToString(), strWhere, " vAllCaiOrderList.ids desc ")); //SQL 优化 strSql = strSql.Replace("SELECT *", "SELECT *,e_LastTime ").Replace(" ) TT", ") TT left join tb_EForm on tb_EForm.allE_id=TT.id and proId=20 "); strSql.Append(" order by TT.Row "); List <VAN_OA.Model.ReportForms.vAllCaiOrderList> list = new List <VAN_OA.Model.ReportForms.vAllCaiOrderList>(); using (SqlConnection conn = DBHelp.getConn()) { conn.Open(); SqlCommand objCommand = new SqlCommand(strSql.ToString(), conn); using (SqlDataReader dataReader = objCommand.ExecuteReader()) { while (dataReader.Read()) { var model = ReaderBindAll(dataReader); object ojb; ojb = dataReader["IsHanShui"]; if (ojb != null && ojb != DBNull.Value) { model.IsHanShui = Convert.ToInt32(ojb) == 0 ? false : true; } ojb = dataReader["ids"]; if (ojb != null && ojb != DBNull.Value) { model.ids = Convert.ToInt32(ojb); } ojb = dataReader["CaiFpType"]; if (ojb != null && ojb != DBNull.Value) { model.CaiFpType = Convert.ToString(ojb); } ojb = dataReader["e_LastTime"]; if (ojb != null && ojb != DBNull.Value) { model.LastTime = (DateTime)ojb; } ojb = dataReader["TruePrice1"]; if (ojb != null && ojb != DBNull.Value) { model.TruePrice1 = (decimal)ojb; } ojb = dataReader["TruePrice2"]; if (ojb != null && ojb != DBNull.Value) { model.TruePrice2 = (decimal)ojb; } ojb = dataReader["TruePrice3"]; if (ojb != null && ojb != DBNull.Value) { model.TruePrice3 = (decimal)ojb; } ojb = dataReader["LastTruePrice"]; if (ojb != null && ojb != DBNull.Value) { model.LastTruePrice = (decimal)ojb; } model.LastTotal = model.lastPrice * model.Num; list.Add(model); } } } return(list); }
private void Show(bool isPage = true) { 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 (txtProDateFrom.Text != "") { if (CommHelp.VerifesToDateTime(txtProDateFrom.Text) == false) { base.ClientScript.RegisterStartupScript(base.GetType(), null, "<script>alert('项目单据时间 格式错误!');</script>"); return; } } if (txtProDateTo.Text != "") { if (CommHelp.VerifesToDateTime(txtProDateTo.Text) == false) { base.ClientScript.RegisterStartupScript(base.GetType(), null, "<script>alert('项目单据时间 格式错误!');</script>"); return; } } if (txtCaiNum.Text != "") { if (CommHelp.VerifesToNum(txtCaiNum.Text) == false) { base.ClientScript.RegisterStartupScript(base.GetType(), null, "<script>alert('数量 格式错误!');</script>"); return; } } if (txtCaiPrice.Text != "") { if (CommHelp.VerifesToNum(txtCaiPrice.Text) == false) { base.ClientScript.RegisterStartupScript(base.GetType(), null, "<script>alert('最终采购单价 格式错误!');</script>"); return; } } if (txtAuditDate.Text != "") { if (CommHelp.VerifesToDateTime(txtAuditDate.Text) == false) { base.ClientScript.RegisterStartupScript(base.GetType(), null, "<script>alert('审批日期 格式错误!');</script>"); return; } } if (txtPONo.Text.Trim() != "") { if (CheckPoNO(txtPONo.Text.Trim()) == false) { return; } } if (txtPONO1.Text.Trim() != "") { if (CheckPoNO(txtPONO1.Text.Trim()) == false) { return; } } if (txtPONO2.Text.Trim() != "") { if (CheckPoNO(txtPONO2.Text.Trim()) == false) { return; } } if (txtPONO3.Text.Trim() != "") { if (CheckPoNO(txtPONO3.Text.Trim()) == false) { return; } } if (txtPoProNo.Text.Trim() != "") { if (CheckProNo(txtPoProNo.Text.Trim()) == false) { return; } } PagerDomain page = new PagerDomain(); if (isPage == false) { page.PageSize = 1000000; } page.CurrentPageIndex = AspNetPager1.CurrentPageIndex; decimal Total = 0; List <vAllCaiOrderList> pOOrderList = this.POSer.GetListArrayAll_Page(GetSql(), page, out Total); foreach (var model in pOOrderList) { if (model.BusType == "0") { model.BusType = "项目订单采购"; } else if (model.BusType == "1") { model.BusType = "库存采购"; } } lblTotal.Text = Total.ToString();// pOOrderList.Sum(t => (t.lastPrice * t.Num)).ToString(); AspNetPager1.RecordCount = page.TotalCount; this.gvMain.PageIndex = AspNetPager1.CurrentPageIndex - 1; this.gvMain.DataSource = pOOrderList; this.gvMain.DataBind(); }
/// <summary> /// 获得数据列表(比DataSet效率高,推荐使用) /// </summary> public List <VAN_OA.Model.JXC.CAI_OrderCheck> GetListArray(string strWhere, PagerDomain page) { StringBuilder strSql = new StringBuilder(); strSql.Append("select * from ("); strSql.Append("select "); strSql.Append("distinct tb_EForm.e_LastTime,IsHanShui,CAI_OrderCheck.Id,CheckPer,CAI_OrderCheck.CheckTime,CAI_OrderCheck.CreatePer,CAI_OrderCheck.CreateTime,CheckRemark ,CheckUser.loginName as CheckUserName,ProNo,Status,AE"); strSql.Append(",PONo,POName,CAI_OrderChecks.GUESTName,SupplierName from CAI_OrderCheck left join CAI_OrderChecks on CAI_OrderChecks.CheckId=CAI_OrderCheck.id left join tb_User as CheckUser on CheckUser.id=CheckPer "); strSql.Append(" left join (select distinct PONo as CGPONO,AE from CAI_POOrder) as tb on tb.CGPONO=CAI_OrderChecks.PONO left join CAI_POCai on CAI_POCai.Ids=CAI_OrderChecks.CaiId "); strSql.Append(" left join tb_EForm on tb_EForm.allE_id=CAI_OrderCheck.id and proId=21 "); if (strWhere.Trim() != "") { strSql.Append(" where " + strWhere); } //strSql.Append(" order by CAI_OrderCheck.Id desc"); strSql.Append(") AS TEMP"); strSql = new StringBuilder(DBHelp.GetPagerSql(page, strSql.ToString(), "", " Id desc ")); List <VAN_OA.Model.JXC.CAI_OrderCheck> list = new List <VAN_OA.Model.JXC.CAI_OrderCheck>(); using (SqlConnection conn = DBHelp.getConn()) { conn.Open(); SqlCommand objCommand = new SqlCommand(strSql.ToString(), conn); using (SqlDataReader dataReader = objCommand.ExecuteReader()) { while (dataReader.Read()) { VAN_OA.Model.JXC.CAI_OrderCheck model = new VAN_OA.Model.JXC.CAI_OrderCheck(); object ojb; ojb = dataReader["Id"]; if (ojb != null && ojb != DBNull.Value) { model.Id = (int)ojb; } ojb = dataReader["CheckPer"]; if (ojb != null && ojb != DBNull.Value) { model.CheckPer = (int)ojb; } ojb = dataReader["CheckTime"]; if (ojb != null && ojb != DBNull.Value) { model.CheckTime = (DateTime)ojb; } ojb = dataReader["CreateTime"]; if (ojb != null && ojb != DBNull.Value) { model.CreateTime = (DateTime)ojb; } ojb = dataReader["e_LastTime"]; if (ojb != null && ojb != DBNull.Value) { model.LastTime = (DateTime)ojb; } model.CheckRemark = dataReader["CheckRemark"].ToString(); //ojb = dataReader["CreateName"]; //if (ojb != null && ojb != DBNull.Value) //{ // model.CreateName = ojb.ToString(); //} ojb = dataReader["CheckUserName"]; if (ojb != null && ojb != DBNull.Value) { model.CheckUserName = ojb.ToString(); } ojb = dataReader["ProNo"]; if (ojb != null && ojb != DBNull.Value) { model.ProNo = ojb.ToString(); } ojb = dataReader["Status"]; if (ojb != null && ojb != DBNull.Value) { model.Status = ojb.ToString(); } ojb = dataReader["PONo"]; if (ojb != null && ojb != DBNull.Value) { model.PONo = ojb.ToString(); } ojb = dataReader["POName"]; if (ojb != null && ojb != DBNull.Value) { model.POName = ojb.ToString(); } ojb = dataReader["GUESTName"]; if (ojb != null && ojb != DBNull.Value) { model.GUESTName = ojb.ToString(); } ojb = dataReader["SupplierName"]; if (ojb != null && ojb != DBNull.Value) { model.SupplierName = ojb.ToString(); } ojb = dataReader["AE"]; if (ojb != null && ojb != DBNull.Value) { model.AE = ojb.ToString(); } ojb = dataReader["IsHanShui"]; if (ojb != null && ojb != DBNull.Value) { model.IsHanShui = (int)ojb; } list.Add(model); } } } return(list); }
private void Show() { string sql = " 1=1 "; //if (txtPONo.Text != "" || ttxPOName.Text != "" || txtSupplier.Text!="") //{ // sql += " and exists(select ids from CAI_OrderChecks where 1=1 "; if (txtRemark.Text.Trim() != "") { sql += string.Format(" and CheckRemark like '%{0}%' ", txtRemark.Text.Trim()); } if (txtPONo.Text.Trim() != "") { if (CheckPoNO(txtPONo.Text.Trim()) == false) { return; } sql += string.Format(" and PONO like '%{0}%' ", txtPONo.Text.Trim()); } if (ttxPOName.Text.Trim() != "") { sql += string.Format(" and POName like '%{0}%' ", ttxPOName.Text.Trim()); } if (txtSupplier.Text.Trim() != "") { if (cbPiPei.Checked) { sql += string.Format(" and SupplierName='{0}'", txtSupplier.Text.Trim()); } else { sql += string.Format(" and SupplierName like '%{0}%'", txtSupplier.Text.Trim()); } } // sql += "and checkId=CAI_OrderCheck.id) "; //} if (txtFrom.Text != "") { if (CommHelp.VerifesToDateTime(txtFrom.Text) == false) { base.ClientScript.RegisterStartupScript(base.GetType(), null, "<script>alert('项目日期 格式错误!');</script>"); return; } sql += string.Format(" and CheckTime>='{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 CheckTime<='{0} 23:59:59'", txtTo.Text); } if (ddlStatue.Text != "") { sql += string.Format(" and Status='{0}'", ddlStatue.Text); } else { sql += string.Format(" and Status<>'不通过'"); } if (txtJianYan.Text != "") { sql += string.Format(" and CheckUser.loginName like '%{0}%'", txtJianYan.Text); } if (txtCaiGou.Text.Trim() != "") { if (CheckProNo(txtCaiGou.Text.Trim()) == false) { return; } sql += string.Format(" and CaiProNo like '%{0}%'", txtCaiGou.Text.Trim()); } if (ddlUser.Text != "-1") { //sql += string.Format(" and (CAI_OrderCheck.CreatePer={0} or exists(select id from CG_POOrder where CG_POOrder.PONo=CAI_OrderChecks.PONo and AppName={0}))", ddlUser.Text); sql += string.Format(" and AE='{0}'", ddlUser.SelectedItem.Text); } if (ddlCompany.Text != "-1") { string where = string.Format(" CompanyCode='{0}'", ddlCompany.Text.Split(',')[2]); sql += string.Format("and (CAI_OrderCheck.CreatePer IN(select id from tb_User where {0}) or exists(select id from CG_POOrder where IFZhui=0 and CG_POOrder.PONo=CAI_OrderChecks.PONo and AppName IN(select id from tb_User where {0})))", where); } if (ddlIsHanShui.Text != "-1") { sql += string.Format(" and CAI_POCai.IsHanShui={0} ", ddlIsHanShui.Text); } if (ddlModel.Text != "全部") { sql += string.Format(" and EXISTS (select ID from CG_POOrder where Model='{0}' AND PONO=CAI_OrderChecks.PONO) ", ddlModel.Text); } PagerDomain page = new PagerDomain(); page.CurrentPageIndex = AspNetPager1.CurrentPageIndex; List <CAI_OrderCheck> pOOrderList = this.POSer.GetListArray(sql, page); string ids = ""; for (int i = 0; i < pOOrderList.Count; i++) { if (pOOrderList[i].Status == "通过") { ids += pOOrderList[i].Id.ToString() + ","; } } ids = ids.Trim(','); if (!string.IsNullOrEmpty(ids)) { string InHouserSql = string.Format(@" select CAI_OrderCheck.Id from CAI_OrderCheck left join CAI_OrderChecks on CAI_OrderCheck.Id=CAI_OrderChecks.CheckId left join CAI_OrderInHouses on CAI_OrderInHouses.OrderCheckIds=CAI_OrderChecks.Ids left join ( select TB_SupplierAdvancePayments.CaiIds from TB_SupplierAdvancePayment left join TB_SupplierAdvancePayments on TB_SupplierAdvancePayments.Id=TB_SupplierAdvancePayment.Id where Status='通过' ) as Yu on Yu.CaiIds=CAI_OrderChecks.CaiId left join ( select TB_SupplierInvoices.RuIds from TB_SupplierInvoice left join TB_SupplierInvoices on TB_SupplierInvoices.Id=TB_SupplierInvoice.Id where Status<>'不通过' and IsYuFu=1 ) as Zhi on Zhi.RuIds=CAI_OrderInHouses.Ids where CAI_OrderCheck.Status='通过' and Yu.CaiIds is not null and Zhi.RuIds is null and CAI_OrderCheck.id in ({0})", ids); var dt = DBHelp.getDataTable(InHouserSql); idsList = new List <string>(); foreach (DataRow dr in dt.Rows) { idsList.Add(dr[0].ToString()); } // InHouserSql = (@" select CAI_OrderCheck.Id from CAI_OrderCheck // left join CAI_OrderChecks on CAI_OrderCheck.Id=CAI_OrderChecks.CheckId // left join CAI_OrderInHouses on CAI_OrderInHouses.OrderCheckIds=CAI_OrderChecks.Ids // // left join // ( // select TB_SupplierAdvancePayments.CaiIds from TB_SupplierAdvancePayment left join TB_SupplierAdvancePayments on TB_SupplierAdvancePayments.Id=TB_SupplierAdvancePayment.Id // where Status='通过' // ) as Yu on Yu.CaiIds=CAI_OrderChecks.CaiId // left join // ( // select TB_SupplierInvoices.RuIds from TB_SupplierInvoice left join TB_SupplierInvoices on TB_SupplierInvoices.Id=TB_SupplierInvoice.Id // where Status<>'不通过' and IsYuFu=1 // ) as Zhi on Zhi.RuIds=CAI_OrderInHouses.Ids // //where CAI_OrderCheck.Status='通过' and Yu.CaiIds is not null and Zhi.RuIds is null "); // dt = DBHelp.getDataTable(InHouserSql); // idsList = new List<string>(); // foreach (DataRow dr in dt.Rows) // { // XiuFu(dr[0].ToString()); // } } AspNetPager1.RecordCount = page.TotalCount; //AspNetPager1.RecordCount = pOOrderList.Count; //this.gvMain.PageIndex = AspNetPager1.CurrentPageIndex - 1; this.gvMain.DataSource = pOOrderList; this.gvMain.DataBind(); //子单 List <CAI_OrderChecks> orders = new List <CAI_OrderChecks>(); gvList.DataSource = orders; gvList.DataBind(); }