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 = "未找到符合条件数据。";
}
}
