public void tlbl(string sql, SqlConnection conn, int zt, string msg)
{
try { conn.Open(); } catch { }
SqlDataAdapter sd2 = new SqlDataAdapter(sql, conn);
try
{
ds.Tables["ylsyl"].Clear();
}
catch { }
sd2.Fill(ds, "ylsyl");
try
{ //与领用做比对
if (ds.Tables["ylsyl"].Rows[0][0].ToString() == "")
{
MessageBox.Show("没有符合" + msg + "的数据");
conn.Close();
return;
}
}
catch
{
MessageBox.Show("没有符合" + msg + "的数据");
conn.Close();
return;
}
tj tj = new tj();
RK ck = new RK(tj);
ck.ShowDialog();
/***************************************************/
ArrayList al = Globals.Return_proc(conn, "24");
string sql_icstockbill = "select FBillerID,FSelTranType, FBrNo, FInterID, FTranType, FBillNo, FDate, FDeptID,Frob,FPurposeID from ICStockBill where 1=2";
SqlDataAdapter icstockbill = new SqlDataAdapter(sql_icstockbill, conn);
try
{
ds.Tables["icstockbill"].Clear();
ds.Tables["icstockbillentry"].Clear();
}
catch { }
icstockbill.Fill(ds, "icstockbill");
DataRow rk = ds.Tables["icstockbill"].NewRow();
rk["FPurposeID"] = 12000;
rk["FBillerID"] = 16394; //制单人
rk["FSelTranType"] = 85;
rk["FBrNo"] = 0;
rk["FInterID"] = al[1];
rk["FTranType"] = 24;
rk["FBillNo"] = al[0];
rk["fdate"] = DateTime.Now.ToString("d");
rk["FDeptID"] = comboBox2.SelectedValue;
rk["Frob"] = zt;
ds.Tables["icstockbill"].Rows.Add(rk);
SqlCommandBuilder sb = new SqlCommandBuilder(icstockbill);
string sql_icstockbillentry = "select FChkPassItem,FSourceInterId,FDCStockID,FSCStockID, FDCSPID,FBrNo,FEntryID,FUnitID,FQty,FAuxQty,FItemID,FInterID,FQtyMust,FAuxQtyMust,FBatchNo,FSourceTranType,FSourceBillNo,FICMOBillNo,FICMOInterID,FCostOBJID from ICStockBillEntry where 1=2";
SqlDataAdapter icstockbillentry = new SqlDataAdapter(sql_icstockbillentry, conn);
icstockbillentry.Fill(ds, "icstockbillentry");
sb = new SqlCommandBuilder(icstockbillentry);
for (int i = 0; i < ds.Tables["ylsyl"].Rows.Count; i++)
{
rk = ds.Tables["icstockbillentry"].NewRow();
rk["FChkPassItem"] = 1058;
rk["FSourceInterId"] = ydnm.Text;
rk["FSCStockID"] = tj.ck;//仓库
if (tj.cw != "0")
{
rk["FDCSPID"] = tj.cw;//仓位
}
rk["FCostOBJID"] = cbdx.Text;
rk["FBrNo"] = 0;
rk["FEntryID"] = i + 1;
rk["FUnitID"] = 259;
rk["FAuxQty"] = rk["FQty"] = ds.Tables["ylsyl"].Rows[i][0];
rk["FItemID"] = ds.Tables["ylsyl"].Rows[i][1];
rk["FInterID"] = al[1];
rk["FAuxQtyMust"] = rk["FQtyMust"] = ds.Tables["ylsyl"].Rows[i][0];
rk["FBatchNo"] = ds.Tables["ylsyl"].Rows[i][2];
//rk["FBatchNo"] = label6.Text;
rk["FSourceTranType"] = 85;
rk["FSourceBillNo"] = comboBox1.Text;
rk["FICMOBillNo"] = comboBox1.Text;
rk["FICMOInterID"] = ydnm.Text;
ds.Tables["icstockbillentry"].Rows.Add(rk);
}
icstockbill.Update(ds.Tables["icstockbill"]);
icstockbillentry.Update(ds.Tables["icstockbillentry"]);
//修改入库任务的工序数据入库状态
MessageBox.Show(msg + "完成。");
conn.Close();
}
private void toolStripButton3_Click(object sender, EventArgs e)
{
if (MessageBox.Show("任务完成后批量退料", "确认", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) == DialogResult.No)
{
return; //否就退出操作
}
SqlCommand sqlcmd = new SqlCommand();
sqlcmd.Connection = conn;
//sqlcmd.Connection.Open();
try
{
// DataSet ds = new DataSet();
try
{
conn.Open();
}
catch { }
//统计材料用量
//string ylsyl = "select sum(FSycd) from Tylcpgx where FCptm in(select FBarcode from Tgx where FICMOBillNo ='"+comboBox1.Text+"' and FWork='印刷')";
// string ylsyl = "select sum( fsycd) * ISNULL(fcoefficient , 1) as yl,fitemid,fcoefficient from V_ylgx where FCptm in(select FBarcode from Tgx where FICMOBillNo ='" + comboBox1.Text + "' and FWork='印刷') group by fitemid,coefficient";
//领用量
// string ly = "select sum(fqty)lyl ,FItemID from v_flrw where FICMOBillNo='"+comboBox1.Text+"' group by FItemID";
// string ylsyl = "select 0-lyl+yl tll,tb1.FItemID,FBatchNo from (select sum( fsycd) * ISNULL(fcoefficient , 1) as yl,fitemid,fcoefficient,FBatchNo from V_ylgx where FCptm in(select FBarcode from Tgx where FICMOBillNo ='" + comboBox1.Text + "' and FWork='分切' and FHj=0) group by fitemid,fcoefficient,FBatchNo)tb1 left join(select sum(fqty)lyl ,FItemID from v_flrw where FICMOBillNo='" + comboBox1.Text + "' group by FItemID )tb2 on tb1.fitemid= tb2.FItemID where tb1.FItemID is not null";
string ylsyl = "select 0-lyl+yl tll,tb1.FItemID,tb1.FBatchNo from (select sum( fsycd) * ISNULL(fcoefficient , 1) as yl,fitemid,fcoefficient,FBatchNo from V_ylgx where FCptm in(select FBarcode from Tgx where FICMOBillNo ='" + comboBox1.Text + "' and FWork='分切' and FHj=0) group by fitemid,fcoefficient,FBatchNo)tb1 left join(select sum(fqty)lyl ,FItemID,FBatchNo from v_flrw where FICMOBillNo='" + comboBox1.Text + "' group by FItemID,FBatchNo ) tb2 on tb1.fitemid= tb2.FItemID and tb1.Fbatchno=tb2.FBatchNo where tb1.fitemid is not null and 0-lyl+yl<0";
SqlDataAdapter sd2 = new SqlDataAdapter(ylsyl, conn);
try
{
ds.Tables["ylsyl"].Clear();
}
catch { }
sd2.Fill(ds, "ylsyl");
try
{ //与领用做比对
if (ds.Tables["ylsyl"].Rows[0][0].ToString() == "")
{
MessageBox.Show("没有符合入库的数据");
conn.Close();
return;
}
}
catch
{
MessageBox.Show("没有符合入库的数据");
conn.Close();
return;
}
tj tj = new tj();
RK ck = new RK(tj);
ck.ShowDialog();
/***************************************************/
// ArrayList al = Globals.Return_proc(conn);
ArrayList al = Globals.Return_proc(conn, "24");
string sql_icstockbill = "select FBillerID,FSelTranType, FBrNo, FInterID, FTranType, FBillNo, FDate, FDeptID,Frob,FPurposeID from ICStockBill where 1=2";
SqlDataAdapter icstockbill = new SqlDataAdapter(sql_icstockbill, conn);
try
{
ds.Tables["icstockbill"].Clear();
ds.Tables["icstockbillentry"].Clear();
}
catch { }
icstockbill.Fill(ds, "icstockbill");
DataRow rk = ds.Tables["icstockbill"].NewRow();
rk["FPurposeID"] = 12000;
rk["FBillerID"] = 16394; //制单人
rk["FSelTranType"] = 85;
rk["FBrNo"] = 0;
rk["FInterID"] = al[1];
rk["FTranType"] = 24;
rk["FBillNo"] = al[0];
rk["fdate"] = DateTime.Now.ToString("d");
rk["FDeptID"] = comboBox2.SelectedValue;
rk["Frob"] = -1;
ds.Tables["icstockbill"].Rows.Add(rk);
SqlCommandBuilder sb = new SqlCommandBuilder(icstockbill);
string sql_icstockbillentry = "select FChkPassItem,FSourceInterId,FDCStockID,FDCSPID,FSCStockID,FBrNo,FEntryID,FUnitID,FQty,FAuxQty,FItemID,FInterID,FQtyMust,FAuxQtyMust,FBatchNo,FSourceTranType,FSourceBillNo,FICMOBillNo,FICMOInterID,FCostOBJID from ICStockBillEntry where 1=2";
SqlDataAdapter icstockbillentry = new SqlDataAdapter(sql_icstockbillentry, conn);
icstockbillentry.Fill(ds, "icstockbillentry");
sb = new SqlCommandBuilder(icstockbillentry);
for (int i = 0; i < ds.Tables["ylsyl"].Rows.Count; i++)
{
rk = ds.Tables["icstockbillentry"].NewRow();
rk["FChkPassItem"] = 1058;
rk["FSourceInterId"] = ydnm.Text;
rk["FSCStockID"] = tj.ck;//仓库
rk["FCostOBJID"] = cbdx.Text;
if (tj.cw != "0")
{
rk["FDCSPID"] = tj.cw;//仓位
}
rk["FBrNo"] = 0;
rk["FEntryID"] = i + 1;
rk["FUnitID"] = 259;
rk["FAuxQty"] = rk["FQty"] = ds.Tables["ylsyl"].Rows[i][0];
rk["FItemID"] = ds.Tables["ylsyl"].Rows[i][1];
rk["FInterID"] = al[1];
rk["FAuxQtyMust"] = rk["FQtyMust"] = ds.Tables["ylsyl"].Rows[i][0];
rk["FBatchNo"] = ds.Tables["ylsyl"].Rows[i][2];
// rk["FBatchNo"] = label6.Text;
rk["FSourceTranType"] = 85;
rk["FSourceBillNo"] = comboBox1.Text;
rk["FICMOBillNo"] = comboBox1.Text;
rk["FICMOInterID"] = ydnm.Text;
ds.Tables["icstockbillentry"].Rows.Add(rk);
}
icstockbill.Update(ds.Tables["icstockbill"]);
icstockbillentry.Update(ds.Tables["icstockbillentry"]);
//修改入库任务的工序数据入库状态
Globals.E_proc_closeicmo(conn, comboBox1.Text);
conn.Close();
/***************************************************/
}
catch { }
string sql = "select FBillNo,FName,khmc,xsdd,fworkshop,fitemid,FNumber,finterid,fqty,FDefaultLoc,FSPID,FGMPBatchNo,FModel,FApproveNo,FWidth,jg,zxbz from v_xdxx where FBillNo in(select FICMOBillNo from V_Flrw) and fworkshop='" + comboBox2.SelectedValue + "'";
comboBox1.DataSource = null;
comboBox1.DataSource = dt = sj.ds(sql, "rw", conn);
comboBox1.DisplayMember = "FBillNo";
comboBox1.ValueMember = "FBillNo";
MessageBox.Show("退料完成");
}