private void MaterialDetail_Load(object sender, EventArgs e)
{
string sqlstr = "select t.matr_seq_no 申请流水号,t.site 域,(select description from IFSAPP.SUB_PROJECT qq where qq.project_id= t.project_id and qq.sub_project_id =(select sub_project_id from IFSAPP.ACTIVITY_SUM_DETAIL p where p.activity_seq = 100086998)) 专业, (select description from IFSAPP.ACTIVITY_SUM_DETAIL pp where pp.activity_seq = t.activity_seq ) 材料种类,t.part_no 零件号,'" + part_name + "' as 零件描述 ,t.p_requisition_no MEO号,t.p_order_no 采购订单号,t.dt_issued 下发日期,t.request_date 需求日期,t.request_qty 需求数量,IFSAPP.PROJ_PROCU_RATION_API.Get_Accu_Ration_Qty(MATR_SEQ_NO) as 已下发数量,t.request_qty -IFSAPP.PROJ_PROCU_RATION_API.Get_Accu_Ration_Qty(MATR_SEQ_NO) as 可用数量,t.user_cre 操作人,t.reason_code 申请原因,t.design_code 范围,t.c_partial_info 范围 from IFSAPP.PROJECT_MISC_PROCUREMENT t where design_code like '%" + design_code + "%' and PROJECT_ID = '" + project_no + "' and site = '" + site_no + "' and issue_from_inv = 0 and PART_NO ='" + part_no + "' and (select state from ifsapp.purchase_req_line_part q where q.requisition_no =p_requisition_no and q.part_no=t.part_no) <>'Cancelled'";
DataSet ds = PartParameter.QueryPartERPInventory(sqlstr);
dgv_meo.DataSource = ds.Tables[0].DefaultView;
string sprojectname = project_no.Substring(project_no.Length - 3, 3);
string sqlstrnew = "select tt.part_no 零件号,tt.part_desc 零件描述,tt.qty_onhand 预留数量,tt.qty_reserved 已用数量,tt.req_dept 预留标识 from ifsapp.yr_inv_on_hand_vw tt WHERE tt.part_no ='" + part_no + "' and tt.contract = '" + site_no + "' and tt.req_dept like 'YL" + sprojectname + "%'";
DataSet dsnew = PartParameter.QueryPartERPInventory(sqlstrnew);
dgv_reserved.DataSource = dsnew.Tables[0].DefaultView;
}
public void listviewBindInter(string sql)
{
this.dgv1.AutoGenerateColumns = false;
//this.dgv1.Rows.Clear();
DataSet ds = MEOsub.QueryPartMiscProcListEPR(sql);
DataView dv = ds.Tables[0].DefaultView;
dgv1.DataSource = dv;
//方法一：逐行比对ERP中申请的数据然后返回比对结果；
if (dgv1.RowCount > 0)
{
for (int i = 0; i < dgv1.RowCount; i++)
{
string part_no = dgv1.Rows[i].Cells["part_no"].Value.ToString();
string requir_qty = dgv1.Rows[i].Cells["require_qty"].Value.ToString();
string meo_no = dgv1.Rows[i].Cells["meo_erp"].Value.ToString();
string sqlstr = "select t.p_requisition_no MEO号,t.request_qty as 申请数量 from IFSAPP.PROJECT_MISC_PROCUREMENT t where PROJECT_ID = '" + Projectid + "' and site = '" + mSite + "' and issue_from_inv = 0 and PART_NO ='" + part_no + "'and p_requisition_no =" + meo_no + " and (select state from ifsapp.purchase_req_line_part q where q.requisition_no =p_requisition_no and q.part_no=t.part_no) <>'Cancelled'";
DataSet dsnew = PartParameter.QueryPartERPInventory(sqlstr);
if (dsnew.Tables[0].Rows.Count != 0)
{
dgv1.Rows[i].Cells["ISEXISTED"].Value = "存在";
string req_qty = dsnew.Tables[0].Rows[i]["申请数量"].ToString();
decimal ecqty = decimal.Parse(requir_qty);
decimal erpqty = decimal.Parse(req_qty);
if (decimal.Round(ecqty, 2) == decimal.Round(erpqty, 2))
{
dgv1.Rows[i].Cells["ERP_QTY"].Value = erpqty;
dgv1.Rows[i].Cells["compare_result"].Value = "数量一致";
}
else
{
dgv1.Rows[i].Cells["ERP_QTY"].Value = erpqty;
dgv1.Rows[i].Cells["compare_result"].Value = "数量不一致";
}
}
else
{
dgv1.Rows[i].Cells["ISEXISTED"].Value = "不存在";
}
}
}
//方法二：将符合域与项目条件的所有ERP申请数据整合到DataSet中去，然后遍历比对数据
}
private string GetPartMEONO(string part_no, string needqty)
{
string MEO_NO = "";
decimal avalQty = 0, reqQty = 0;
reqQty = Convert.ToDecimal(needqty);
string sqlstr = "select t.p_requisition_no MEO号,t.request_qty -IFSAPP.PROJ_PROCU_RATION_API.Get_Accu_Ration_Qty(MATR_SEQ_NO) as 可用数量 from IFSAPP.PROJECT_MISC_PROCUREMENT t where PROJECT_ID = '" + ProjectId + "' and site = '" + Site + "' and issue_from_inv = 0 and PART_NO ='" + part_no + "'";
DataSet ds = PartParameter.QueryPartERPInventory(sqlstr);
if (ds != null)
{
int p = ds.Tables[0].Rows.Count;
for (int i = 0; i < p; i++)
{
avalQty = Convert.ToDecimal(ds.Tables[0].Rows[i][1].ToString());
string TempMEONo = ds.Tables[0].Rows[i][0].ToString();
if (avalQty > 0)
{
if (Convert.ToDecimal(reqQty) > avalQty)
{
MEO_NO = MEO_NO + TempMEONo + ";";
reqQty = reqQty - avalQty;
}
else
{
MEO_NO = MEO_NO + TempMEONo + ";";
reqQty = 0;
break;
}
}
}
if (reqQty > 0)
{
MEO_NO = MEO_NO + GetPartReserveNO(part_no, reqQty.ToString());
}
return(MEO_NO.Contains(';') ? MEO_NO.Substring(0, MEO_NO.Length - 1) : MEO_NO);
}
else
{
return(GetPartReserveNO(part_no, reqQty.ToString()));
}
}
private string GetPartReserveNO(string part_no, string needqty)
{
string sprojectname = ProjectId.Substring(ProjectId.Length - 3, 3);
string MEO_NO = "";
string sqlstrnew = "select (tt.qty_onhand - tt.qty_reserved) 可用数量,tt.req_dept 预留标识 from ifsapp.yr_inv_on_hand_vw tt WHERE tt.part_no ='" + part_no + "' and tt.contract = '" + Site + "' and tt.req_dept like 'YL" + sprojectname + "%'";
DataSet dsnew = PartParameter.QueryPartERPInventory(sqlstrnew);
decimal avalQty = 0, reqQty = 0;
reqQty = Convert.ToDecimal(needqty);
if (dsnew != null)
{
int p = dsnew.Tables[0].Rows.Count;
for (int i = 0; i < p; i++)
{
avalQty = Convert.ToDecimal(dsnew.Tables[0].Rows[i][0].ToString());
string TempMEONo = dsnew.Tables[0].Rows[i][1].ToString();
if (avalQty > 0)
{
if (Convert.ToDecimal(reqQty) > avalQty)
{
MEO_NO = MEO_NO + TempMEONo + ";";
reqQty = reqQty - avalQty;
}
else
{
MEO_NO = MEO_NO + TempMEONo + ";";
reqQty = 0;
break;
}
}
}
return(MEO_NO);
}
else
{
return(MEO_NO);
}
}
public void listviewBind(string sql)
{
//更改并设置列名称以及属性
DataSet ds = PartParameter.QueryPartERPInventory(sql);
// dgv1.DataSource = ds.Tables[0];
//DataGridViewComboBoxColumn dgvcom = new DataGridViewComboBoxColumn();
//dgvcom.DataSource = PartParameter.QueryPartPara("select name from mm_unit_tab").Tables[0].DefaultView;;
//dgvcom.DisplayMember = "name";
//dgvcom.ValueMember = "name";
////dgv1.Columns.Insert(1, dgvcom);
//dgv1.GridColumnStyles[1]=dgvcom;
//dgv1.Columns["零件认证"].Width = 100;
//dgv1.Columns["单位密度"].ValueType = typeof();
//dgv1.Columns["零件材质"].ValueType = typeof(float);
if (ds != null)
{
this.dgv1.AutoGenerateColumns = false;
this.dgv1.Rows.Clear();
this.dgv1.Columns.Clear();
this.dgv1.Columns.Add("Id", "序号");
this.dgv1.Columns.Add("域", "域");
this.dgv1.Columns.Add("零件号", "零件号");
this.dgv1.Columns.Add("零件描述", "零件描述");
this.dgv1.Columns.Add("申请数量", "申请数量");
this.dgv1.Columns.Add("已下发数量", "已下发数量");
this.dgv1.Columns.Add("余下申请数量", "余下申请数量");
this.dgv1.Columns.Add("项目预留可用", "项目预留可用");
this.dgv1.Columns.Add("项目总的数量", "项目总的数量");
this.dgv1.Columns.Add("单位", "单位");
dgv1.Columns["Id"].ReadOnly = true;
dgv1.Columns["域"].ReadOnly = true;
dgv1.Columns["零件号"].ReadOnly = true;
dgv1.Columns["申请数量"].ReadOnly = true;
dgv1.Columns["已下发数量"].ReadOnly = true;
dgv1.Columns["余下申请数量"].ReadOnly = true;
dgv1.Columns["项目预留可用"].ReadOnly = true;
dgv1.Columns["项目总的数量"].ReadOnly = true;
dgv1.Columns["单位"].ReadOnly = true;
//dgv1.Columns["单位"].ReadOnly = true;
dgv1.Columns["零件描述"].ReadOnly = true;
DataView dv = ds.Tables[0].DefaultView;
int i = 1;
foreach (DataRow dr in dv.Table.Rows)
{
DataGridViewRow r = new DataGridViewRow();
r.CreateCells(dgv1);
r.Cells[0].Value = i.ToString();
r.Cells[1].Value = dr[0].ToString();
r.Cells[2].Value = dr[1].ToString();
r.Cells[3].Value = dr[2].ToString();
r.Cells[4].Value = dr[5].ToString();
r.Cells[5].Value = dr[6].ToString();
r.Cells[6].Value = dr[7].ToString();
r.Cells[7].Value = dr[4].ToString();
r.Cells[8].Value = Convert.ToDecimal(dr[4].ToString()) + Convert.ToDecimal(dr[7].ToString());
r.Cells[9].Value = dr[3].ToString();
if (Convert.ToDecimal(dr[5].ToString()) + Convert.ToDecimal(dr[8].ToString()) != 0)
{
this.dgv1.Rows.Add(r);
i++;
}
}
}
}