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