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