protected void Page_Load(object sender, EventArgs e) { Response.BufferOutput = true; Authentication auth = new Authentication(this); if (auth.LoadSession() == false) { auth.RemoveSession(); Response.Redirect("../../UI/FrameUI/login.htm"); Response.End(); } else { m_perimission = ((Authentication.LOGININFO)Session["USERINFO"]).Permission; if (CheckAccessAble()) { if (!IsPostBack) { GVRation.DataSource = null; GVRation.DataBind(); DdlProjectBind(); DdlProdSiteBind(); DdlReceiptDeptBind(); DdlReceiptPersonBind(); } } else { auth.RemoveSession(); Response.Redirect("../../UI/FrameUI/login.htm"); Response.End(); } } }
private void GVRationDataBind() { string projectId = this.DdlProject.SelectedValue; string partNo = this.TxtPartNo.Text; string mtrNo = Misc.ToDBC(TxtMtrNo.Text.Trim()); string mtrSeqNo = Misc.ToDBC(TxtMtrSeqNo.Text.Trim()); StringBuilder sqlstr = new StringBuilder("select "); sqlstr.Append("b.project_id,"); sqlstr.Append("a.part_no,a.c_partial_info block,"); sqlstr.Append("ifsapp.inventory_part_api.get_description@erp_prod(a.site,a.part_no) part_description,"); sqlstr.Append("b.misc_tab_ref_no,"); sqlstr.Append("b.material_req_seq_no,"); sqlstr.Append("b.ration_qty,"); sqlstr.Append("nvl(b.issued_qty,0) issued_qty,"); sqlstr.Append("b.activity_seq,"); sqlstr.Append("ifs_data_api.get_all_activity_desc(b.activity_seq) activity_desc,"); /*用api取jp_requisition表对应ration行的申请总数,考虑jp_requisition行的状态,状态限制不等于“cancelled”,如果有并发用户,校验时这个数有可能已经不准确了*/ sqlstr.Append("nvl(jp_requisition_api.get_required_num_of_mtr(b.misc_tab_ref_no,b.material_req_seq_no),0) REQUESTED_QTY,");//配送单中的数量 sqlstr.Append("b.ration_qty-nvl(jp_requisition_api.get_required_num_of_mtr(b.misc_tab_ref_no,b.material_req_seq_no),0) REMAIN_QTY,"); sqlstr.Append("a.issue_from_inv "); sqlstr.Append(",a.DESIGN_CODE,a.C_PARTIAL_INFO PARTIAL_INFO "); //ming.li 20130321 10:50 增加物料属性 sqlstr.Append(" from "); sqlstr.Append("IFSAPP.PROJECT_MISC_PROCUREMENT@erp_prod a,"); sqlstr.Append("IFSAPP.PROJ_PROCU_RATION@erp_prod b "); sqlstr.Append(" where "); sqlstr.Append(" a.matr_seq_no=b.misc_tab_ref_no "); if (!(projectId == "" || projectId == "0")) { sqlstr.Append(string.Format(" and a.project_id='{0}'", projectId)); } if (partNo != "") { sqlstr.Append(string.Format(" and a.part_no ='{0}'", partNo)); } if (mtrNo != "") { string[] _mtrs; int _p = mtrNo.IndexOf(".."); if (_p > 0) { sqlstr.Append(string.Format(" and b.misc_tab_ref_no>=to_number('{0}') and b.misc_tab_ref_no<=to_number('{1}')", mtrNo.Substring(0, _p), mtrNo.Substring(_p + 2))); } else { _mtrs = mtrNo.Split(';'); if (_mtrs.Length > 1) { sqlstr.Append(" and b.misc_tab_ref_no in ("); for (int i = 0; i < _mtrs.Length; i++) { sqlstr.Append(string.Format("'{0}'", _mtrs[i])); if (i < _mtrs.Length - 1) { sqlstr.Append(","); } } sqlstr.Append(")"); } else { sqlstr.Append(string.Format(" and to_char(b.misc_tab_ref_no)='{0}'", mtrNo)); } } } if (mtrSeqNo != "") { string[] _mtrs; int _p = mtrSeqNo.IndexOf(".."); if (_p > 0) { sqlstr.Append(string.Format(" and b.material_req_seq_no>=to_number('{0}') and b.material_req_seq_no<=to_number('{1}')", mtrSeqNo.Substring(0, _p), mtrSeqNo.Substring(_p + 2))); } else { _mtrs = mtrSeqNo.Split(';'); if (_mtrs.Length > 1) { sqlstr.Append(" and b.material_req_seq_no in ("); for (int i = 0; i < _mtrs.Length; i++) { sqlstr.Append(string.Format("'{0}'", _mtrs[i])); if (i < _mtrs.Length - 1) { sqlstr.Append(","); } } sqlstr.Append(")"); } else { sqlstr.Append(string.Format(" and to_char(b.material_req_seq_no)='{0}'", mtrSeqNo)); } } } sqlstr.Append(" and b.ration_qty > nvl(b.issued_qty,0)"); //sqlstr.Append(" and ((a.issue_from_inv=0"); //sqlstr.Append(" and ifsapp.PROJ_PROCU_RATION_API.Get_Misc_Tab_Num_Info@erp_prod(b.MISC_TAB_REF_NO, 'QTY_RECEIVED') > nvl(b.issued_qty,0))"); //sqlstr.Append(" or a.issue_from_inv = 1)"); GVRation.DataSource = Lib.DBHelper.createGridView(sqlstr.ToString()); GVRation.DataBind(); }
protected void BtnShowRation_Click(object sender, EventArgs e) { DataBox.Visible = true; LblMsg.Text = ""; string projectId = this.DdlProject.SelectedValue; string partNo = this.TxtPartNo.Text; string mtrNo = TxtMtrNo.Text; StringBuilder sqlstr = new StringBuilder("select a.site,a.part_no,ifsapp.inventory_part_api.Get_Description@erp_prod(a.site,a.part_no) part_name, "); sqlstr.Append("b.project_id,"); sqlstr.Append("b.purch_part_no,"); sqlstr.Append("b.misc_tab_ref_no,"); sqlstr.Append("b.material_req_seq_no,"); sqlstr.Append("b.ration_qty,"); sqlstr.Append("nvl(b.issued_qty,0) issued_qty,"); sqlstr.Append("b.activity_seq,"); sqlstr.Append("ifs_data_api.get_all_activity_desc(b.activity_seq) activity_desc,"); /*用api取jp_requisition表对应ration行的申请总数,考虑jp_requisition行的状态,状态限制不等于“canceled”,如果有并发用户,校验时这个数有可能已经不准确了*/ sqlstr.Append("nvl(jp_requisition_api.get_required_num_of_mtr(b.misc_tab_ref_no,b.material_req_seq_no),0) REQUESTED_QTY,"); sqlstr.Append("b.ration_qty-nvl(jp_requisition_api.get_required_num_of_mtr(b.misc_tab_ref_no,b.material_req_seq_no),0) REMAIN_QTY,"); sqlstr.Append("a.issue_from_inv "); sqlstr.Append(",a.DESIGN_CODE,a.C_PARTIAL_INFO PARTIAL_INFO "); //ming.li 20130321 10:50 增加物料属性 sqlstr.Append(" from "); sqlstr.Append("IFSAPP.PROJECT_MISC_PROCUREMENT@erp_prod a,"); sqlstr.Append("IFSAPP.PROJ_PROCU_RATION@erp_prod b "); sqlstr.Append(",ifsapp.purchase_part@erp_prod c");// ming.li 20130318 11:35 添加c表 sqlstr.Append(" where "); sqlstr.Append(" a.matr_seq_no=b.misc_tab_ref_no "); sqlstr.Append(" and c.contract=a.site and a.part_no=c.part_no "); // ming.li 20130318 11:35 c表与a表的关联 //ming.li 20130318 10:55 项目改成非必填 //sqlstr.Append(string.Format(" and a.project_id='{0}'", projectId)); if (!(projectId == "" || projectId == "0")) { sqlstr.Append(string.Format(" and a.project_id='{0}'", projectId)); } if (partNo != "") { sqlstr.Append(string.Format(" and a.part_no ='{0}'", partNo)); } if (mtrNo != "") { sqlstr.Append(string.Format(" and b.misc_tab_ref_no='{0}'", mtrNo)); } sqlstr.Append(" and b.ration_qty > nvl(b.issued_qty,0)"); //sqlstr.Append(" and ("); //sqlstr.Append(" ifsapp.PROJ_PROCU_RATION_API.Get_Misc_Tab_Num_Info@erp_prod(b.MISC_TAB_REF_NO, 'QTY_RECEIVED') > nvl(b.issued_qty,0)"); //sqlstr.Append(" or a.issue_from_inv = 1)"); if (TxtPartName.Text.Trim() != "") { sqlstr.Append(string.Format(" and c.description like '{0}'", TxtPartName.Text)); } GVRation.DataSource = Lib.DBHelper.createGridView(sqlstr.ToString()); GVRation.DataBind(); if (GVRation.Rows.Count == 0) { DataBox.Visible = false; LblMsg.Text = "未找到符合条件数据。"; } }