private void btnSelect_Click(object sender, EventArgs e)
{
DgvOpt.SetShow(DgvMain);
DgvMain.DataSource = null;
DataTable dtShow = null;
if (CheckErrData())
{
if (Msg.Show("存在排程上线数量与已绑定工单数量不一致，是否确定继续查询？", "提示", MessageBoxButtons.YesNoCancel) == DialogResult.Yes)
{
dtShow = GetShowDt();
}
}
else
{
dtShow = GetShowDt();
}
if (dtShow != null)
{
SetPlanKid(GetPlanKid());
}
DtOpt.DtDateFormat(dtShow, "日期");
DgvOpt.SetShow(DgvMain, dtShow);
DgvOpt.SetColWidth(DgvMain, "生产单号", 180);
UI();
}
private void GetInfo()
{
string slqStr = @"SELECT RTRIM(TD004) 品号, RTRIM(TD005) 品名, RTRIM(TD006) 规格, RTRIM(TD053) 配置方案, CAST(TD008 AS FLOAT) ERP订单数量, ISNULL(SC013, 0) 已排数量
FROM dbo.COPTD
LEFT JOIN (SELECT SC001, SUM(SC013) SC013 FROM WG_DB.dbo.SC_PLAN GROUP BY SC001) AS SC_PLAN ON RTRIM(TD001)+'-'+RTRIM(TD002)+'-'+RTRIM(TD003) = SC001
WHERE RTRIM(TD001)+'-'+RTRIM(TD002)+'-'+RTRIM(TD003) = '{0}'";
DataTable dt = mssql.SQLselect(connYF, string.Format(slqStr, textBoxDd.Text));
string sqlStr2 = @"SELECT SC013 上线数量, SC003 上线日期, SC023 生产车间 FROM SC_PLAN WHERE K_ID = '{0}' AND SC001 = '{1}' ";
DataTable dt2 = mssql.SQLselect(connWG, string.Format(sqlStr2, index, dd));
if (dt2 != null)
{
DtOpt.DtDateFormat(dt2, "日期");
dateTimePicker1.Text = dt2.Rows[0]["上线日期"].ToString();
comboBoxDpt.Text = dt2.Rows[0]["生产车间"].ToString();
textBoxSl.Text = dt2.Rows[0]["上线数量"].ToString();
}
if (dt != null)
{
label3.Text = "品号：" + dt.Rows[0]["品号"].ToString();
label4.Text = "品名：" + dt.Rows[0]["品名"].ToString();
label5.Text = "规格：" + dt.Rows[0]["规格"].ToString();
label6.Text = "配置方案：" + dt.Rows[0]["配置方案"].ToString();
label10.Text = "ERP订单数量：" + dt.Rows[0]["ERP订单数量"].ToString();
label11.Text = "已排数量：" + dt.Rows[0]["已排数量"].ToString();
if (textBoxSl.Text == "")
{
textBoxSl.Text = (int.Parse(dt.Rows[0]["ERP订单数量"].ToString()) - int.Parse(dt.Rows[0]["已排数量"].ToString())).ToString();
}
}
}
private void GetPreErpData(int rowIndex)
{
ignore = true;
string dd = DgvPreDo.Rows[rowIndex].Cells["生产单号"].Value.ToString();
string sqlStr1 = @"SELECT RTRIM(MA002) 客户名称, RTRIM(TD004) 品号, RTRIM(TD005) 品名, RTRIM(TD006) 规格, RTRIM(TD053) 客户配置, CAST(TD008 AS FLOAT) ERP订单数量, TD013 交货日期
FROM COPTC(NOLOCK)
INNER JOIN COPTD(NOLOCK) ON TD001 = TC001 AND TD002 = TC002
LEFT JOIN COPMA(NOLOCK) ON MA001 = TC004
WHERE RTRIM(TD001) + '-' + RTRIM(TD002) + '-' + RTRIM(TD003) = '{0}'";
string sqlStr2 = @"SELECT ISNULL(CAST(SUM(SC013) AS FLOAT), 0) 已排数量 FROM SC_PLAN(NOLOCK) WHERE SC001 = '{0}' GROUP BY SC001 ";
string sqlStr3 = @"SELECT ISNULL(COUNT(SC001), 0) 已排行数 FROM SC_PLAN(NOLOCK) WHERE SC001 = '{0}' GROUP BY SC001, SC003, SC013, SC023 ";
DataTable dt1 = mssql.SQLselect(connYF, string.Format(sqlStr1, dd));
DataTable dt2 = mssql.SQLselect(connWG, string.Format(sqlStr2, dd));
DataTable dt3 = mssql.SQLselect(connWG, string.Format(sqlStr3, dd));
if (dt1 != null)
{
DtOpt.DtDateFormat(dt1, "日期");
DgvPreDo.Rows[rowIndex].Cells["客户名称"].Value = dt1.Rows[0]["客户名称"].ToString();
DgvPreDo.Rows[rowIndex].Cells["品号"].Value = dt1.Rows[0]["品号"].ToString();
DgvPreDo.Rows[rowIndex].Cells["品名"].Value = dt1.Rows[0]["品名"].ToString();
DgvPreDo.Rows[rowIndex].Cells["规格"].Value = dt1.Rows[0]["规格"].ToString();
DgvPreDo.Rows[rowIndex].Cells["客户配置"].Value = dt1.Rows[0]["客户配置"].ToString();
DgvPreDo.Rows[rowIndex].Cells["ERP订单数量"].Value = dt1.Rows[0]["ERP订单数量"].ToString();
DgvPreDo.Rows[rowIndex].Cells["交货日期"].Value = dt1.Rows[0]["交货日期"].ToString();
}
else
{
DgvPreDo.Rows[rowIndex].Cells["品号"].Value = "不存在ERP信息";
}
if (dt2 != null)
{
DgvPreDo.Rows[rowIndex].Cells["已排数量"].Value = dt2.Rows[0]["已排数量"].ToString();
}
else
{
DgvPreDo.Rows[rowIndex].Cells["已排数量"].Value = "0";
}
if (dt3 != null)
{
DgvPreDo.Rows[rowIndex].Cells["已排行数"].Value = dt3.Rows[0]["已排行数"].ToString();
}
else
{
DgvPreDo.Rows[rowIndex].Cells["已排行数"].Value = "0";
}
ignore = false;
}
private void ShowDoneData(int rowIndex)
{
DataTable dt = GetPlanData(rowIndex);
if (dt != null)
{
DtOpt.DtDateFormat(dt, "日期");
DgvDone.DataSource = dt;
DgvOpt.SetColWidth(DgvDone, "生产单号", 150);
}
else
{
DgvDone.DataSource = null;
}
}
private void DgvShow()
{
string slqStr = @"SELECT ISNULL(MOCTA.UDF02, '') 排程序号, RTRIM(TA001) 工单别, RTRIM(TA002) 工单号, TA003 开单日期,
CAST(TA015 AS FLOAT) 预计产量, RTRIM(ME002) 生产部门,
(CASE TA011 WHEN '1' THEN '未生产' WHEN '2' THEN '已发料' WHEN '3' THEN '生产中' WHEN 'Y' THEN '已完工' WHEN 'y' THEN '指定结束' END) 工单状态,
(CASE TA013 WHEN 'N' THEN '未审核' WHEN 'Y' THEN '已审核' ELSE TA013 END) 审核码,
RTRIM(TA006) 品号, RTRIM(MB002) 品名, RTRIM(MB003) 规格
FROM MOCTA
LEFT JOIN CMSME ON TA064 = ME001
LEFT JOIN INVMB ON MB001 = TA006
WHERE 1=1
AND RTRIM(TA026) + '-' + RTRIM(TA027) +'-'+ RTRIM(TA028) = '{0}' ";
if (CheckBoxScGd.Checked && !CheckBoxScGd.Checked)
{
slqStr += @" AND TA001 = '5121' ";
}
if (!CheckBoxScGd.Checked && CheckBoxScGd.Checked)
{
slqStr += @" AND TA001 = '5101' ";
}
if (CheckBoxScGd.Checked && CheckBoxScGd.Checked)
{
slqStr += @" AND TA001 IN ('5101', '5121') ";
}
if (CheckBoxSameIndex.Checked)
{
slqStr += string.Format(@" AND MOCTA.UDF02 = '{0}' ", TextBoxIndex.Text);
}
if (CheckBoxSameWlno.Checked)
{
slqStr += string.Format(@" AND MOCTA.TA006 = '{0}' ", TextBoxWlno.Text);
}
slqStr += " ORDER BY RTRIM(TA001), RTRIM(TA002) ";
DataTable dt = mssql.SQLselect(connYF, string.Format(slqStr, TextBoxDd.Text));
if (dt != null)
{
DgvMain.DataSource = dt;
DtOpt.DtDateFormat(dt, "日期");
DgvOpt.SetColMiddleCenter(DgvMain);
DgvOpt.SetColWidth(DgvMain, "规格", 180);
}
}
private void btnSelect_Click(object sender, EventArgs e)
{
DgvOpt.SetShow(DgvMain);
DgvMain.DataSource = null;
DataTable dtShow = null;
dtShow = GetShowDt();
DtOpt.DtDateFormat(dtShow, "日期");
DgvOpt.SetShow(DgvMain, dtShow);
DgvOpt.SetColHeadMiddleCenter(DgvMain);
DgvOpt.SetColMiddleCenter(DgvMain, "量");
DgvOpt.SetColMiddleCenter(DgvMain, "库存");
DgvOpt.SetColWidth(DgvMain, "量", 60);
DgvOpt.SetColWidth(DgvMain, "库存", 60);
DgvOpt.SetColWidth(DgvMain, "单位", 40);
DgvOpt.SetColWidth(DgvMain, "品名", 200);
DgvOpt.SetColWidth(DgvMain, "规格", 350);
DgvOpt.SetColWidth(DgvMain, "供应商编号", 50);
DgvOpt.SetColWidth(DgvMain, "供应商简称", 150);
DgvOpt.SetColReadonly(DgvMain);
UI();
}
private DataTable GetData1()
{
string sqlStr = @"SELECT RTRIM(MOCTA.TA001)+'-'+RTRIM(MOCTA.TA002) 工单编号, MOCTA.TA040 审核日期, MOCTA.TA003 开单日期,
RTRIM(MB1.MB001) 产品品号, RTRIM(MB1.MB004) 产品单位, RTRIM(MB1.MB002) 产品品名, RTRIM(MB1.MB003) 产品规格,
CAST(MOCTA.TA015 AS FLOAT) 预计产量, CAST(MOCTA.TA016 AS FLOAT) 已领套数, CAST(MOCTA.TA017 AS FLOAT) 已生产数量, CAST(MOCTA.TA018 AS FLOAT) 报废数量,
(CASE MOCTA.TA013 WHEN 'Y' THEN '已审核' WHEN 'N' THEN '未审核' WHEN 'U' THEN '破核' WHEN 'V' THEN '作废' WHEN 'y' THEN '指定结束' ELSE MOCTA.TA013 END) 审核码,
(CASE MOCTA.TA011 WHEN '1' THEN '未生产' WHEN '2' THEN '已领料' WHEN '3' THEN '生产中' WHEN 'y' THEN '指定结束' WHEN 'Y' THEN '已完工' ELSE MOCTA.TA013 END) 状态码,
MOCTA.TA012 实际开工日期, MOCTA.TA014 实际完工日期,
RTRIM(MB2.MB001) 材料品号, RTRIM(MB2.MB004) 材料单位, RTRIM(MB2.MB002) 材料品名, RTRIM(MB2.MB003) 材料规格,
CAST(ROUND(SUM(-1 * INVLA.LA005 * INVLA.LA011), 2) AS FLOAT) 领用数量, (CASE WHEN SUM(-1 * INVLA.LA005 * INVLA.LA011) = 0 THEN 0 ELSE CAST(ROUND(SUM(-1 * INVLA.LA005 * INVLA.LA013)/SUM(-1 * INVLA.LA005 * INVLA.LA011), 8) AS NUMERIC(16, 8)) END) 单位成本, CAST(SUM(-1 * INVLA.LA005 * INVLA.LA013) AS NUMERIC(16, 8)) 领用成本,
RTRIM(MOCTA.TA057) 生产批号, RTRIM(MOCTA.TA058) 批号说明,
(CASE MOCTB.TB011 WHEN '1' THEN '直接材料' WHEN '2' THEN '间接材料' WHEN '3' THEN '供应商供料' WHEN '4' THEN '不发料' WHEN '5' THEN '客户供料' ELSE MOCTB.TB011 END) 材料类型,
(CASE MOCTB.TBC03 WHEN '1' THEN '工程品号' WHEN '2' THEN '正式品号' ELSE MOCTB.TBC03 END) 类型
FROM MOCTA
INNER JOIN MOCTB ON MOCTA.TA001 = MOCTB.TB001 AND MOCTA.TA002 = MOCTB.TB002
INNER JOIN INVMB AS MB1 ON MB1.MB001 = MOCTA.TA006
INNER JOIN INVMB AS MB2 ON MB2.MB001 = MOCTB.TB003
LEFT JOIN MOCTE ON MOCTE.TE011 = MOCTB.TB001 AND MOCTE.TE012 = MOCTB.TB002 AND MOCTE.TE004 = MOCTB.TB003 AND MOCTE.TE009 = MOCTB.TB006
LEFT JOIN MOCTC ON MOCTE.TE001 = MOCTC.TC001 AND MOCTE.TE002 = MOCTC.TC002
LEFT JOIN INVLA ON INVLA.LA006 = MOCTE.TE001 AND INVLA.LA007 = MOCTE.TE002 AND INVLA.LA008 = MOCTE.TE003
WHERE MOCTA.TA013 IN ('Y')
AND MOCTC.TC009 IN ('Y') ";
if (DtpStartFinishedDate.Checked)
{
sqlStr += string.Format(@"AND MOCTA.TA014 >= '{0}' ", DtpStartFinishedDate.Value.ToString("yyyyMMdd"));
}
if (DtpEndFinishedDate.Checked)
{
sqlStr += string.Format(@"AND MOCTA.TA014 <= '{0}' ", DtpEndFinishedDate.Value.ToString("yyyyMMdd"));
}
if (DtpStartBeginDate.Checked)
{
sqlStr += string.Format(@"AND MOCTA.TA012 >= '{0}' ", DtpStartBeginDate.Value.ToString("yyyyMMdd"));
}
if (DtpEndBeginDate.Checked)
{
sqlStr += string.Format(@"AND MOCTA.TA012 <= '{0}' ", DtpEndBeginDate.Value.ToString("yyyyMMdd"));
}
if (TextBoxGdBegin.Text != "")
{
sqlStr += string.Format(@"AND RTRIM(MOCTA.TA001)+'-'+RTRIM(MOCTA.TA002) >= '{0}' ", TextBoxGdBegin.Text);
}
if (TextBoxGdEnd.Text != "")
{
sqlStr += string.Format(@"AND RTRIM(MOCTA.TA001)+'-'+RTRIM(MOCTA.TA002) <= '{0}' ", TextBoxGdEnd.Text);
}
sqlStr += GetStatuSqlStr();
sqlStr += @" GROUP BY RTRIM(MOCTA.TA001)+'-'+RTRIM(MOCTA.TA002), MOCTA.TA040, MOCTA.TA003,
RTRIM(MB1.MB001), RTRIM(MB1.MB004), RTRIM(MB1.MB002), RTRIM(MB1.MB003),
CAST(MOCTA.TA015 AS FLOAT), CAST(MOCTA.TA016 AS FLOAT), CAST(MOCTA.TA017 AS FLOAT), CAST(MOCTA.TA018 AS FLOAT),
(CASE MOCTA.TA013 WHEN 'Y' THEN '已审核' WHEN 'N' THEN '未审核' WHEN 'U' THEN '破核' WHEN 'V' THEN '作废' WHEN 'y' THEN '指定结束' ELSE MOCTA.TA013 END),
(CASE MOCTA.TA011 WHEN '1' THEN '未生产' WHEN '2' THEN '已领料' WHEN '3' THEN '生产中' WHEN 'y' THEN '指定结束' WHEN 'Y' THEN '已完工' ELSE MOCTA.TA013 END),
MOCTA.TA012, MOCTA.TA014,
RTRIM(MB2.MB001), RTRIM(MB2.MB004), RTRIM(MB2.MB002), RTRIM(MB2.MB003),
RTRIM(MOCTA.TA057), RTRIM(MOCTA.TA058),
(CASE MOCTB.TB011 WHEN '1' THEN '直接材料' WHEN '2' THEN '间接材料' WHEN '3' THEN '供应商供料' WHEN '4' THEN '不发料' WHEN '5' THEN '客户供料' ELSE MOCTB.TB011 END),
(CASE MOCTB.TBC03 WHEN '1' THEN '工程品号' WHEN '2' THEN '正式品号' ELSE MOCTB.TBC03 END)
ORDER BY RTRIM(MOCTA.TA001)+'-'+RTRIM(MOCTA.TA002), RTRIM(MB2.MB001) ";
DataTable dt = mssql.SQLselect(connSW, sqlStr);
DtOpt.DtDateFormat(dt, "日期");
if (dt != null)
{
dt.TableName = "工单领料成本明细表";
}
return(dt);
}
private void Init()
{
BtnInput.Enabled = false;
DgvOpt.SetRowBackColor(DgvPreDo);
DgvOpt.SetColHeadMiddleCenter(DgvPreDo);
DgvOpt.SetRowBackColor(DgvDone);
DgvOpt.SetColHeadMiddleCenter(DgvDone);
if (inputDt != null)
{
inputDt.Columns.Add("状态");
inputDt.Columns["状态"].SetOrdinal(0);
if (inputDt.Columns.Contains("ERP订单数量"))
{
inputDt.Columns.Remove("ERP订单数量");
}
inputDt.Columns.Add("ERP订单数量");
if (inputDt.Columns.Contains("已排数量"))
{
inputDt.Columns.Remove("已排数量");
}
inputDt.Columns.Add("已排数量");
if (inputDt.Columns.Contains("品号"))
{
inputDt.Columns.Remove("品号");
}
inputDt.Columns.Add("品号");
if (inputDt.Columns.Contains("品名"))
{
inputDt.Columns.Remove("品名");
}
inputDt.Columns.Add("品名");
if (inputDt.Columns.Contains("规格"))
{
inputDt.Columns.Remove("规格");
}
inputDt.Columns.Add("规格");
if (inputDt.Columns.Contains("客户配置"))
{
inputDt.Columns.Remove("客户配置");
}
inputDt.Columns.Add("客户配置");
if (inputDt.Columns.Contains("客户名称"))
{
inputDt.Columns.Remove("客户名称");
}
inputDt.Columns.Add("客户名称");
if (inputDt.Columns.Contains("交货日期"))
{
inputDt.Columns.Remove("交货日期");
}
inputDt.Columns.Add("交货日期");
if (inputDt.Columns.Contains("已排行数"))
{
inputDt.Columns.Remove("已排行数");
}
inputDt.Columns.Add("已排行数");
DtOpt.DtDateFormat(inputDt, "日期");
DgvPreDo.DataSource = inputDt;
DgvOpt.SetColHeadMiddleCenter(DgvPreDo);
DgvOpt.SetColReadonly(DgvPreDo);
DgvOpt.SetColMiddleCenter(DgvPreDo, "状态");
DgvOpt.SetColMiddleCenter(DgvPreDo, "日期");
DgvOpt.SetColMiddleCenter(DgvPreDo, "数量");
DgvOpt.SetColWritable(DgvPreDo, "上线数量");
DgvOpt.SetColWidth(DgvPreDo, "生产单号", 150);
}
else
{
Msg.ShowErr("无传入的数据！");
}
UI();
}
private void Dgv_Show()
{
if (dgv_Main.DataSource != null)
{
dgv_Main.DataSource = null;
}
string sqlstr = "SELECT SC.SC001 订单号, SC003 上线日期, "
+ "SC010 品名, SC013 数量, SC036 纸箱编码, SC040 纸箱尺寸, "
+ "SC037 订单编码, '' 订单类别, MD_No 栈板号, ISNULL(PDCOUNT, 0) 已过机数量, "
+ "(CASE SC033 WHEN '1' THEN 'Y' ELSE 'N'END ) 已完成, PD2.MIXDATE 最早过机时间, PD2.MAXDATE 最迟过机时间 "
+ "FROM SCHEDULE AS SC "
+ "LEFT JOIN ( "
+ "SELECT SC001, MD_No, COUNT(Pd_Sta) PDCOUNT FROM PdData "
+ "WHERE Pd_Sta = 'OK' GROUP BY SC001, MD_No "
+ ") AS PD ON PD.SC001 = SC.SC001 "
+ "LEFT JOIN ( "
+ "SELECT SC001, MIN(Pd_date) MIXDATE, MAX(Pd_date) MAXDATE FROM PdData "
+ "WHERE Pd_Sta = 'OK' GROUP BY SC001 "
+ ") AS PD2 ON PD2.SC001 = SC.SC001 "
+ "WHERE 1 = 1 ";
if (TestFlag)
{
sqlstr += "AND SC.SC039 = 'Y' ";
}
else
{
if (FindFlag)
{
sqlstr += "AND SC.SC001 LIKE '%" + FindStr.Trim() + "%' ";
FindStr = "";
}
else
{
sqlstr += "AND SC.SC003 = '" + dateTimePicker1.Value.ToString("yyyyMMdd") + "' AND SC.SC039 != 'Y' ";
}
}
sqlstr += "ORDER BY KEY_ID ";
Main_dt = mssql.SQLselect(connMD, sqlstr);
if (Main_dt != null)
{
DtOpt.DtDateFormat(Main_dt, "上线日期");
dgv_Main.DataSource = Main_dt;
DgvOpt.SetRowBackColor(dgv_Main);
if (TestFlag)
{
btn_Test_Save.Enabled = true;
dgv_Main.ReadOnly = false;
List <int> list = new List <int>();
list.Add(3);
list.Add(4);
list.Add(5);
list.Add(6);
list.Add(7);
DgvOpt.SetColWritable(dgv_Main, list);
}
else
{
btn_Test_Save.Enabled = false;
dgv_Main.ReadOnly = true;
}
dgv_Main.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
DgvOpt.SetColMiddleCenter(dgv_Main, "生产日期");
DgvOpt.SetColMiddleCenter(dgv_Main, "数量");
DgvOpt.SetColMiddleCenter(dgv_Main, "纸箱编码");
DgvOpt.SetColMiddleCenter(dgv_Main, "纸箱尺寸");
DgvOpt.SetColMiddleCenter(dgv_Main, "订单编码");
DgvOpt.SetColMiddleCenter(dgv_Main, "栈板号");
DgvOpt.SetColMiddleCenter(dgv_Main, "已过机数量");
DgvOpt.SetColMiddleCenter(dgv_Main, "已完成");
DgvOpt.SetColWidth(dgv_Main, "最早过机时间", 135);
DgvOpt.SetColWidth(dgv_Main, "最迟过机时间", 135);
DgvOpt.SetColWidth(dgv_Main, "订单号", 130);
DgvOpt.SetColWidth(dgv_Main, "品名", 250);
}
else
{
btn_Test_Save.Enabled = false;
MessageBox.Show("没有查询到数据", "错误");
}
}
