private void DgvShow()
{
string sqlStr = @"SELECT * FROM dbo.V_GetAutoErpPlanOrderDetail ORDER BY UDF12, planDd ";
DataTable dt = mssql.SQLselect(connYF, sqlStr);
if (dt != null)
{
for (int colIndex = 0; colIndex < dt.Columns.Count; colIndex++)
{
if (dt.Columns[colIndex].ColumnName == "planDd")
{
dt.Columns[colIndex].ColumnName = "订单号";
}
if (dt.Columns[colIndex].ColumnName == "planId")
{
dt.Columns[colIndex].ColumnName = "计划编号";
}
if (dt.Columns[colIndex].ColumnName == "LRPCOUNT")
{
dt.Columns[colIndex].ColumnName = "已跑计划次数";
}
if (dt.Columns[colIndex].ColumnName == "CG")
{
dt.Columns[colIndex].ColumnName = "已存在采购单";
}
if (dt.Columns[colIndex].ColumnName == "UDF12")
{
dt.Columns[colIndex].ColumnName = "订单更新时间";
}
if (dt.Columns[colIndex].ColumnName == "TD004")
{
dt.Columns[colIndex].ColumnName = "成品品号";
}
if (dt.Columns[colIndex].ColumnName == "TD053")
{
dt.Columns[colIndex].ColumnName = "客户配置";
}
}
DgvMain.DataSource = dt;
DgvOpt.SetColHeadMiddleCenter(DgvMain);
DgvOpt.SetColMiddleCenter(DgvMain);
DgvOpt.SetColWidth(DgvMain, "订单号", 180);
DgvOpt.SetColWidth(DgvMain, "计划编号", 180);
DgvOpt.SetColWidth(DgvMain, "客户配置", 250);
}
else
{
DgvMain.DataSource = null;
Msg.Show("没有数据");
}
}
//品号信息
private DataTable GetData1()
{
string sqlStr = @"SELECT rtrim(TA001) 单别,rtrim(TA002) 单号,rtrim(TA003) 日期,rtrim(TA006) 产品品号,rtrim(TB003) 材料品号,
rtrim(TB012) 材料品名,rtrim(TB013) 规格,rtrim(TB006) 工艺,rtrim(TB004) 需领用量,rtrim(TB005) 已领用量,rtrim(MW002) 工艺名称,
CAST(TA015 AS FLOAT) 预计产量, rtrim(TB028) 配置方案,rtrim(TA026) 订单单别,rtrim(TA027) 订单单号,rtrim(TA028) 订单序号 FROM MOCTA
INNER JOIN MOCTB ON TA001=TB001 AND TA002=TB002
LEFT JOIN CMSMW ON TB006=MW001
where TA003 BETWEEN '{0}' AND '{1}'
";
DataTable dt = mssql.SQLselect(connYF, string.Format(sqlStr, dateTimePicker1.Value.ToString("yyyyMMdd"), dateTimePicker2.Value.ToString("yyyyMMdd")));
dt.TableName = "工单明细";
return(dt);
}
private void Init()
{
string slqStr = "SELECT Dpt FROM dbo.SC_PLAN_DPT_TYPE WHERE Valid = 1 AND Type = 'In' ORDER BY K_ID";
DataTable dt = mssql.SQLselect(connWG, slqStr);
if (dt != null)
{
for (int rowIdx = 0; rowIdx < dt.Rows.Count; rowIdx++)
{
comboBoxDpt.Items.Add(dt.Rows[rowIdx][0].ToString());
}
}
Dpt = null;
}
DataTable GetShowDt()
{
string sqlStr = @"SELECT RTRIM(TR001) TR001, RTRIM(TR002) TR002, RTRIM(TR003) TR003, TR004, TR009, TR017, CB015, CB004, MB002, TR1.TR200
FROM COPTR AS TR1
INNER JOIN INVMB ON MB001 = TR009
LEFT JOIN BOMCB ON CB001 = TR004 AND CB005 = TR009
WHERE 1=1
AND NOT EXISTS (SELECT 1 FROM COPTR AS TR2 WHERE TR2.TR001 = TR1.TR001 AND TR2.TR002 = TR1.TR002 AND SUBSTRING(TR2.TR003, 1, LEN(TR2.TR003) - 3) = SUBSTRING(TR1.TR003, 1, LEN(TR1.TR003) - 3) AND TR017 = 'Y')
AND TR001 LIKE '1%'
AND TR004 LIKE '2%'
AND CB015 = 'Y'
ORDER BY RTRIM(TR001), RTRIM(TR002), TR004, RTRIM(TR003), CB004, MB002";
DataTable dt = mssql.SQLselect(connYF, sqlStr);
return(dt);
}
private void BoxSizeSelect()
{
string sqlStr = "SELECT RTRIM(MB001) 品号, RTRIM(MB003) 规格 FROM dbo.INVMB WHERE 1=1 AND MB025 = 'P' "
+ "AND MB109 = 'Y' AND (MB002 LIKE '%纸箱%' OR MB002 LIKE '%彩盒%' OR MB002 LIKE '%天地盖%') ORDER BY MB001 ";
DataTable dt = mssql.SQLselect(connYF, sqlStr);
if (dt != null)
{
for (int rowIndex = 0; rowIndex < dt.Rows.Count; rowIndex++)
{
string wlno = dt.Rows[rowIndex]["品号"].ToString();
string spec = dt.Rows[rowIndex]["规格"].ToString();
if (Normal.GetSubstringCount(spec, "*") < 2)
{
continue;
}
else
{
string sizeStr = spec.Split('/')[1];
if (Normal.GetSubstringCount(sizeStr, "*") == 2)
{
int L = 0;
int W = 0;
int H = 0;
try
{
L = int.Parse(sizeStr.Split('*')[0].Split('(')[0].Split('（')[0]);
W = int.Parse(sizeStr.Split('*')[1].Split('(')[0].Split('（')[0]);
H = int.Parse(sizeStr.Split('*')[2].Split('(')[0].Split('（')[0]);
}
catch
{
continue;
}
finally
{
if (L != 0 && H != 0 && W != 0)
{
BoxSizeUpdate(wlno, L.ToString() + "*" + W.ToString() + "*" + H.ToString(), L * H * W);
}
}
}
}
}
}
}
private string GetHttpURL()
{
try
{
string sqlstr = "SELECT ServerURL FROM WG_CONFIG WHERE ConfigName='APP_Server' AND Type='Local' AND Valid = 'Y'";
var get = mssql.SQLselect(connWG, sqlstr).Rows[0][0].ToString();
return(get);
}
catch
{
if (Msg.ShowErr("获取后台服务器配置失败，请联系咨询部！") == DialogResult.OK)
{
Environment.Exit(0);
}
return(null);
}
}
private void ShowDgv()
{
string slqStr = @"SELECT (CASE Type WHEN 'In' THEN '导入' WHEN 'Out' THEN '导出' ELSE Type END) 类型, Dpt 排程部门, ERPDpt ERP部门编号, DptWorkTime 部门总生产工时, Valid 有效码
FROM dbo.SC_PLAN_DPT_TYPE ORDER BY Type, K_ID ";
showDt = mssql.SQLselect(connWG, slqStr);
DgvMain.DataSource = null;
if (showDt != null)
{
DgvMain.DataSource = showDt;
DgvOpt.SetColReadonly(DgvMain, "类型");
DgvOpt.SetColReadonly(DgvMain, "排程部门");
DgvOpt.SetRowBackColor(DgvMain);
DgvOpt.SetColNoSortMode(DgvMain);
DgvOpt.SetColMiddleCenter(DgvMain);
}
}
//品号信息
private DataTable GetData1()
{
string sqlStr = @"SELECT
RTRIM(MB001) AS 品号,
RTRIM(MB002) AS 品名,
RTRIM(MB003) AS 规格,
RTRIM(MB004) AS 单位,
CAST(MB064 AS FLOAT) AS 数量,
RTRIM(MB032) AS 供应商编码,
RTRIM(MA002) 供应商简称,
(CASE WHEN MB109 = 'Y' THEN '已核准' WHEN MB109 = 'y' THEN '尚未核准' WHEN MB109 = 'N' THEN '不准交易' END ) AS 核准状况
FROM INVMB
LEFT JOIN PURMA ON MA001 = MB032
ORDER BY MB109 DESC , MB025, MB001
";
DataTable dt = mssql.SQLselect(connSW, sqlStr);
dt.TableName = "品号信息";
return(dt);
}
private void Work()
{
string sqlStr1 = @"SELECT TD001+'-'+TD002+TD003 FROM COPTD
INNER JOIN INVMB ON MB001=TD004 AND MB025='M'
WHERE NOT EXISTS (SELECT 1 FROM MOCTB_Group WHERE TA026=RTRIM(TD001) AND TA027=RTRIM(TD002) AND TA028=RTRIM(TD003) )
AND EXISTS (SELECT 1 FROM MOCTA AS A WHERE A.TA026=TD001 AND A.TA027=TD002 AND A.TA028=TD003)
AND LEFT(COPTD.CREATE_DATE, 6) >= '202104'
";
string sqlStr2 = @"EXEC P_SetMOCTBGroup '{0}' ";
DataTable dt = mssql.SQLselect(connYF, sqlStr1);
if (dt != null)
{
for (int row = 0; row < dt.Rows.Count; row++)
{
mssql.SQLexcute(connYF, string.Format(sqlStr2, dt.Rows[row][0].ToString()));
log(string.Format("Dd: {0}", dt.Rows[row][0].ToString()));
}
}
}
private string GetFlowID(string flowId = null)
{
string time = mssql.SQLselect(connStr, "SELECT dbo.f_getTime(1) ").Rows[0][0].ToString();
if (flowId == null)
{
flowId = "XH" + time + "0001";
}
else
{
if (mssql.SQLselect(connStr, string.Format("SELECT RTRIM(XHXA005) FROM dbo.XH_LYXA WHERE XHXA005 = '{0}' ", flowId)) == null)
{
return(GetFlowID(flowId));
}
}
return(flowId);
}
/// <summary>
/// 维护采购单单头产品系列
/// </summary>
private void Fix1()
{
string sqlStr1 = @"SELECT TC001, TC002 FROM PURTC WHERE (PURTC.UDF08 IS NULL OR PURTC.UDF07 IS NULL) AND PURTC.TC003 >= '20210429' AND PURTC.TC001 IN ('3301', '3308') ORDER BY TC003, TC001, TC002 ";
string sqlStr2 = @"UPDATE PURTC SET
PURTC.UDF07 =
ISNULL(STUFF((SELECT DISTINCT ',' + INVMB.UDF12 FROM PURTD(NOLOCK)
INNER JOIN PURTR(NOLOCK) ON PURTD.TD001+'-'+PURTD.TD002+'-'+PURTD.TD003 = PURTR.TR019
INNER JOIN PURTB(NOLOCK) ON PURTB.TB001 = PURTR.TR001 AND PURTB.TB002 = PURTR.TR002 AND PURTB.TB003 = PURTR.TR003
INNER JOIN COPTD(NOLOCK) ON COPTD.TD001 = PURTB.TB029 AND COPTD.TD002 = PURTB.TB030 AND COPTD.TD003 = PURTB.TB031
INNER JOIN INVMB(NOLOCK) ON INVMB.MB001 = COPTD.TD004
WHERE PURTC.TC001 = PURTD.TD001 AND PURTC.TC002 = PURTD.TD002
AND ISNULL(INVMB.UDF12, '') != ''
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, ''), ''),
PURTC.UDF08 =
ISNULL(STUFF((SELECT DISTINCT ',' + INVMB.UDF11 FROM PURTD(NOLOCK)
INNER JOIN PURTR(NOLOCK) ON PURTD.TD001+'-'+PURTD.TD002+'-'+PURTD.TD003 = PURTR.TR019
INNER JOIN PURTB(NOLOCK) ON PURTB.TB001 = PURTR.TR001 AND PURTB.TB002 = PURTR.TR002 AND PURTB.TB003 = PURTR.TR003
INNER JOIN COPTD(NOLOCK) ON COPTD.TD001 = PURTB.TB029 AND COPTD.TD002 = PURTB.TB030 AND COPTD.TD003 = PURTB.TB031
INNER JOIN INVMB(NOLOCK) ON INVMB.MB001 = COPTD.TD004
WHERE PURTC.TC001 = PURTD.TD001 AND PURTC.TC002 = PURTD.TD002
AND ISNULL(INVMB.UDF11, '') != ''
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, ''), '')
FROM PURTC(NOLOCK)
WHERE PURTC.TC001 = '{0}' AND PURTC.TC002 = '{1}' " ;
DataTable dt = mssql.SQLselect(connYF, sqlStr1);
if (dt != null)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
string tc001 = dt.Rows[i]["TC001"].ToString();
string tc002 = dt.Rows[i]["TC002"].ToString();
log(string.Format("Fix UDF07, UDF08: TC001: {0}, TC002: {1}!", tc001, tc002));
mssql.SQLexcute(connYF, string.Format(sqlStr2, tc001, tc002));
}
}
}
//品号信息
private DataTable GetData1()
{
string sqlStr = @"SELECT RTRIM(LA001) 品号, RTRIM(MB002) 品名, RTRIM(MB003) 规格, LA004 日期, LA006 单别, RTRIM(LA007) 单号, LA008 序号, RTRIM(LA009) 仓库,
CONVERT(FLOAT, LA011) 单据交易库存数量, CONVERT(FLOAT, LA012) 单据单位成本, LA013 金额, LA017 金额材料,
RTRIM(LA016) 批号, RTRIM(PURMA.MA002) 供应商, RTRIM(LA024) 对象,
RTRIM(TC001) + '-' + RTRIM(TC002) + '-' + RTRIM(TD003) 订单号, TC004 订单客户编号, COPMA.MA002 客户名称
FROM INVLA
INNER JOIN INVMB ON MB001 = LA001
INNER JOIN PURMA ON PURMA.MA001 = LA016
LEFT JOIN MOCTA ON RTRIM(TA001) + '-' + RTRIM(TA002) = LA024
LEFT JOIN COPTC ON TA026 = TC001 AND RTRIM(TA027) = RTRIM(TC002)
LEFT JOIN COPTD ON TA026 = TD001 AND RTRIM(TA027) = RTRIM(TD002) AND TA028 = TD003
LEFT JOIN COPMA ON COPMA.MA001 = TC004
WHERE 1=1
AND LA004 BETWEEN '{0}' AND '{1}'
AND (LA006 LIKE '5%' OR LA006 LIKE '1%')
ORDER BY LA004, LA006, LA007, LA008
";
DataTable dt = mssql.SQLselect(conn, string.Format(sqlStr, dateTimePicker1.Value.ToString("yyyyMMdd"), dateTimePicker2.Value.ToString("yyyyMMdd")));
dt.TableName = "领退料明细";
return(dt);
}
/// <summary>
/// 根据传入的SQL获取客户配置的范围
/// </summary>
/// <param name="slqStr">SQL语句</param>
/// <returns>客户配置Dt</returns>
private DataTable GetKhpzDt(string slqStr)
{
DataTable dt = mssql.SQLselect(connYF, slqStr);
return(dt);
}
//进货单本币税前<>发票本币税前
private DataTable GetData1(string date)
{
string slqStr = @"SELECT TA003,TB008,TB001,TB002,TB003,TB005,TB006,TB007,TB017,TH047
FROM ACPTA LEFT JOIN ACPTB ON TA001=TB001 AND TA002=TB002
LEFT JOIN PURTH ON TB005=TH001 AND TB006=TH002 AND TB007=TH003
WHERE SUBSTRING(TA003,1,6)='202009' AND SUBSTRING(TB008,1,6)='{0}'
AND TA024='Y' AND TA079='1' --AND TB004='1'
AND TB017<>TH047
ORDER BY TB001,TB002,TB003";
DataTable dt = mssql.SQLselect(connYF, string.Format(slqStr, date));
if (dt == null)
{
dt = GetNulData();
}
dt.TableName = "进货单本币税前<>发票本币税前";
return(dt);
}
private string GetTime()
{
string slqStr = @"SELECT LEFT(dbo.f_getTime(1), 14) ";
return(mssql.SQLselect(connYF, slqStr).Rows[0][0].ToString());
}
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 DgvShow()
{
string slqStr = @"SELECT '' AS 上线日期,
(CASE WHEN TC004='0118' THEN '内销' ELSE '外销' END) 订单类型,
(RTRIM(COPTD.TD001) +'-'+ RTRIM(COPTD.TD002) +'-'+COPTD.TD003+(CASE WHEN COPTF.UDF51='1' THEN '(新增)' WHEN COPTF.UDF51='0' THEN '(变更)' ELSE '' END)) AS 生产单号,
RTRIM(COPMA.MA002) AS 客户名称,
RTRIM(COPTC.TC015) AS 注意事项,
(CASE WHEN COPTF.TF003 IS NULL THEN '' WHEN COPTF.TF003 IS NOT NULL AND COPTF.TF017 = 'Y' THEN '指定结束'+':'+'变更版本号'+COPTF.TF003+'_'+COPTF.UDF11 ELSE '变更版本号'+COPTF.TF003+'_'+COPTF.UDF11 END) AS 订单变更原因,
(CASE WHEN COPTD.TD013 = '' THEN '' WHEN COPTD.TD013 IS NULL THEN '' ELSE SUBSTRING(COPTD.TD013, 1, 4) + '-' + SUBSTRING(COPTD.TD013, 5, 2) + '-' + SUBSTRING(COPTD.TD013, 7, 2) END) AS 出货日,
(CASE WHEN COPTD.UDF03 = '' THEN '' WHEN COPTD.UDF03 IS NULL THEN '' ELSE SUBSTRING(COPTD.UDF03, 1, 4) + '-' + SUBSTRING(COPTD.UDF03, 5, 2) + '-' + SUBSTRING(COPTD.UDF03, 7, 2) END) AS 验货日,
RTRIM(COPTD.UDF01) AS PO#,
RTRIM(COPTD.TD005) AS 品名,
RTRIM(COPTD.UDF08) AS 保友品名,
RTRIM(COPTD.TD006) AS 规格,
RTRIM(COPTD.TD008) AS 订单数量,
RTRIM(COPTD.TD024) AS 赠品测试量,
RTRIM(COPTD.TD053) AS 配置方案,
RTRIM(COPTQ.TQ003) AS 配置方案描述,
(COPTQ.UDF07+COPTD.TD020) AS 描述备注,
RTRIM(COPTD.TD204) AS 柜型柜数,
RTRIM(COPTC.TC035) AS 目的地,
RTRIM(CMSMV.MV002) AS 业务员,
RTRIM(COPTC.TC012) AS 客户单号,
RTRIM(COPTD.TD014) AS 客户品号,
RTRIM((CASE WHEN TC004='0118' THEN INVMB.UDF04 ELSE INVMB.UDF05 END)) AS 生产车间,
RTRIM(COPTD.UDF05) AS 客户编码,
RTRIM(COPTD.UDF10) AS 电商编码,
(CASE WHEN COPTC.UDF09='否' THEN '' ELSE '是' END) AS 急单,
SUBSTRING(COPTD.CREATE_DATE,1,12) AS 录单日期,
(CASE WHEN K.TDUDF52 IS NOT NULL THEN '是' ELSE '' END) 订单日期等于BOM日期
FROM COPTD AS COPTD
Left JOIN COPTC AS COPTC On COPTD.TD001=COPTC.TC001 and COPTD.TD002=COPTC.TC002
Left JOIN COPTQ AS COPTQ On COPTD.TD053=COPTQ.TQ002 and COPTD.TD004=COPTQ.TQ001
Left JOIN COPMA AS COPMA On COPTC.TC004=COPMA.MA001
Left JOIN CMSMV AS CMSMV On COPTC.TC006=CMSMV.MV001
LEFT JOIN INVMB AS INVMB ON COPTD.TD004=INVMB.MB001
Left JOIN COPTF AS COPTF On COPTD.TD001=COPTF.TF001 and COPTD.TD002=COPTF.TF002 and COPTD.TD003=COPTF.TF104 AND COPTF.TF003 = (SELECT MAX(TF003) FROM COPTF WHERE COPTD.TD001=COPTF.TF001 and COPTD.TD002=COPTF.TF002 and COPTD.TD003=COPTF.TF104)
LEFT JOIN (SELECT TD001 AS TDTD001, TD002 AS TDTD002, TD003 AS TDTD003, TD013 AS TDTD013, UDF52 AS TDUDF52 FROM COPTD AS COPTD) K ON TDTD001 = TD001 AND TDTD002 = TD002 AND TDTD003 = TD003 AND CONVERT(INT, SUBSTRING(COPTD.CREATE_DATE, 1, 8)) = CONVERT(INT, TDUDF52) AND CONVERT(INT, TD013) - CONVERT(INT, TDUDF52) <=2
LEFT JOIN CMSME AS CMSME ON CMSME.ME001 = INVMB.MB445
WHERE 1=1 AND (COPTC.TC027 = 'Y') ";
if (DtpStartDate.Checked)
{
slqStr += @"AND COPTD.UDF12 > '" + DtpStartDate.Value.ToString("yyyyMMddhhmmss") + "' ";
}
if (DtpEndDate.Checked)
{
slqStr += @"AND COPTD.UDF12 < '" + DtpEndDate.Value.ToString("yyyyMMddhhmmss") + "' ";
}
if (CmBoxType.Text == "生产三部")
{
slqStr += @"AND RTRIM(COPTD.TD005)LIKE '%NUM%' ";
}
if (CmBoxType.Text == "原材料")
{
slqStr += @"AND (COPTD.TD004 LIKE '3%' OR COPTD.TD004 LIKE '4%' ) ";
}
if (CmBoxType.Text == "半成品")
{
slqStr += @"AND COPTD.TD004 LIKE '2%' ";
}
slqStr += @"ORDER BY TD002, TD003 ";
if (!CmBoxType.Items.Contains(CmBoxType.Text))
{
Msg.ShowErr("订单类型选择错误， 请重新选择");
}
else
{
DgvMain.DataSource = null;
DataTable showDt = mssql.SQLselect(connYF, slqStr);
if (showDt != null)
{
DgvMain.DataSource = showDt;
DgvOpt.SetRowBackColor(DgvMain);
DgvMain.Columns[2].Width = 180;
DgvMain.ReadOnly = true;
DgvMain.Columns[0].ReadOnly = false;
BtnOutput.Enabled = true;
}
else
{
Msg.ShowErr("没有查询到数据");
BtnOutput.Enabled = false;
}
}
}
private void InitGdDt()
{
infObjLL.gdDt = mssql.SQLselect(infObjLL.connYF, "SELECT TOP 1 * FROM V_GetWscGd ");
infObjLL.gdDt.Rows.RemoveAt(0);
}
private DataTable GetShowDt()
{
string sqlStr = @"SELECT
材料品号, 材料品名, 材料规格,
需领数量, 已领数量, 未领数量,
主供应商批号库存, 次供应商批号库存, 非主次供应商批号库存,
欠量,
ISNULL(主供应商待检量, 0) 主供应商待检量, ISNULL(次供应商待检量, 0) 次供应商待检量, ISNULL(非主次供应商待检量, 0) 非主次供应商待检量, 单位,
主供应商编号, 主供应商简称, 次供应商编号, 次供应商简称
FROM (
SELECT RTRIM(INVMB2.MB001) 材料品号, RTRIM(INVMB2.MB002) 材料品名, RTRIM(INVMB2.MB003) 材料规格, RTRIM(INVMB2.MB004) 单位,
CAST(SUM(TB004) AS FLOAT) 需领数量, CAST(SUM(CASE WHEN TB005>TB004 THEN TB004 ELSE TB005 END) AS FLOAT) 已领数量, CAST(SUM(CASE WHEN TB005>TB004 THEN 0 ELSE TB004-TB005 END) AS FLOAT) 未领数量,
ISNULL(CAST(INVML.ML005 AS FLOAT), 0) 主供应商批号库存, ISNULL(CAST(INVML2.ML005 AS FLOAT), 0) 次供应商批号库存, ISNULL(CAST(INVML3.ML005 AS FLOAT), 0) 非主次供应商批号库存,
(CASE WHEN CAST(SUM(CASE WHEN TB005>TB004 THEN 0 ELSE TB004-TB005 END) AS FLOAT) > (ISNULL(CAST(INVML.ML005 AS FLOAT), 0) + ISNULL(CAST(INVML2.ML005 AS FLOAT), 0) + ISNULL(CAST(INVML3.ML005 AS FLOAT), 0)) THEN CAST(SUM(CASE WHEN TB005>TB004 THEN 0 ELSE TB004-TB005 END) AS FLOAT) - ISNULL(CAST(INVML.ML005 AS FLOAT), 0) - ISNULL(CAST(INVML2.ML005 AS FLOAT), 0) - ISNULL(CAST(INVML3.ML005 AS FLOAT), 0) ELSE 0 END) 欠量,
RTRIM(PURMA1.MA001) 主供应商编号, RTRIM(PURMA1.MA002) 主供应商简称, RTRIM(PURMA2.MA001) 次供应商编号, RTRIM(PURMA2.MA002) 次供应商简称
FROM WG_DB.dbo.SC_PLAN(NOLOCK) AS SCPLAN
INNER JOIN dbo.MOCTA(NOLOCK) ON MOCTA.UDF02 = SCPLAN.K_ID
INNER JOIN dbo.MOCTB(NOLOCK) ON MOCTA.TA001 = MOCTB.TB001 AND MOCTA.TA002 = MOCTB.TB002
INNER JOIN dbo.COPTD(NOLOCK) ON RTRIM(COPTD.TD001)+'-'+RTRIM(COPTD.TD002)+'-'+RTRIM(COPTD.TD003) = SCPLAN.SC001
INNER JOIN dbo.INVMB(NOLOCK) ON INVMB.MB001 = COPTD.TD004
INNER JOIN dbo.INVMB(NOLOCK) AS INVMB2 ON INVMB2.MB001 = MOCTB.TB003
LEFT JOIN dbo.PURMA(NOLOCK) AS PURMA1 ON PURMA1.MA001 = INVMB2.MB032
LEFT JOIN dbo.PURMA(NOLOCK) AS PURMA2 ON PURMA2.MA001 = INVMB2.UDF06
LEFT JOIN dbo.PURMA(NOLOCK) AS PURMA3 ON PURMA3.MA001 = COPTD.UDF09
LEFT JOIN (
SELECT ML001, ML004, SUM(ML005) AS ML005 FROM INVML WHERE ML002 NOT IN ('P06', 'P05', 'P09', 'P10', 'P11', 'P15', 'P20', 'P21') GROUP BY ML001, ML004
) AS INVML ON INVML.ML001 = INVMB2.MB001 AND INVML.ML004 = PURMA1.MA001
LEFT JOIN (
SELECT ML001, ML004, SUM(ML005) AS ML005 FROM INVML WHERE ML002 NOT IN ('P06', 'P05', 'P09', 'P10', 'P11', 'P15', 'P20', 'P21') GROUP BY ML001, ML004
) AS INVML2 ON INVML2.ML001 = INVMB2.MB001 AND INVML2.ML004 = PURMA2.MA001 AND PURMA1.MA001 != PURMA2.MA001
LEFT JOIN (
SELECT ML001, SUM(ML005) AS ML005 FROM INVML INNER JOIN INVMB ON MB001 = ML001
WHERE ML002 NOT IN ('P06', 'P05', 'P09', 'P10', 'P11', 'P15', 'P20', 'P21') AND ML004 NOT IN (MB032, INVMB.UDF06) GROUP BY ML001
) AS INVML3 ON INVML3.ML001 = INVMB2.MB001
WHERE 1=1" ;
if (!checkBoxFinished.Checked)
{
sqlStr += @" AND MOCTA.TA011 IN ('1', '2', '3') ";
}
if (!checkBoxCgR.Checked)
{
sqlStr += @" AND INVMB2.MB034 IN ('L') ";
}
sqlStr += @"
AND MOCTA.TA011 NOT IN ('y')
AND MOCTA.TA013 NOT IN ('U', 'V')
AND MOCTB.TB011 NOT IN ('4')
AND INVMB2.MB025 IN ('P') " ;
sqlStr += string.Format(@" AND SCPLAN.SC003 >= '{0}' ", DtpStartDate.Value.ToString("yyyyMMdd"));
sqlStr += string.Format(@" AND SCPLAN.SC003 <= '{0}' ", DtpEndDate.Value.ToString("yyyyMMdd"));
sqlStr += string.Format(@" AND (INVMB2.MB001 LIKE '%{0}%' OR INVMB2.MB002 LIKE '%{0}%' OR INVMB2.MB003 LIKE '%{0}%') ", TxbWl.Text);
sqlStr += string.Format(@" AND (PURMA1.MA001 LIKE '%{0}%' OR PURMA1.MA002 LIKE '%{0}%' OR PURMA2.MA001 LIKE '%{0}%' OR PURMA2.MA002 LIKE '%{0}%')", TxbPh.Text);
sqlStr += @"GROUP BY RTRIM(INVMB2.MB001), RTRIM(INVMB2.MB002), RTRIM(INVMB2.MB003), RTRIM(INVMB2.MB004), INVML.ML005, INVML2.ML005, INVML3.ML005, RTRIM(PURMA1.MA001), RTRIM(PURMA1.MA002), RTRIM(PURMA2.MA001), RTRIM(PURMA2.MA002) ";
if (CheckBoxQl.Checked)
{
sqlStr += @"HAVING (CASE WHEN CAST(SUM(CASE WHEN TB005>TB004 THEN 0 ELSE TB004-TB005 END) AS FLOAT) > (ISNULL(CAST(INVML.ML005 AS FLOAT), 0) + ISNULL(CAST(INVML2.ML005 AS FLOAT), 0) + ISNULL(CAST(INVML3.ML005 AS FLOAT), 0)) THEN CAST(SUM(CASE WHEN TB005>TB004 THEN 0 ELSE TB004-TB005 END) AS FLOAT) - ISNULL(CAST(INVML.ML005 AS FLOAT), 0) - ISNULL(CAST(INVML2.ML005 AS FLOAT), 0) - ISNULL(CAST(INVML3.ML005 AS FLOAT), 0) ELSE 0 END) >0 ";
}
sqlStr += @") AS A
LEFT JOIN (
SELECT TG005, TH004, CAST(SUM(TH007) AS FLOAT) 主供应商待检量
FROM PURTG INNER JOIN PURTH ON TG001 = TH001 AND TG002 = TH002 WHERE TG013 = 'N' AND TH030 = 'N' GROUP BY TG005, TH004
) AS PURTG1 ON PURTG1.TH004 = 材料品号 AND PURTG1.TG005 = 主供应商编号
LEFT JOIN (
SELECT TG005, TH004, CAST(SUM(TH007) AS FLOAT) 次供应商待检量
FROM PURTG INNER JOIN PURTH ON TG001 = TH001 AND TG002 = TH002 WHERE TG013 = 'N' AND TH030 = 'N' GROUP BY TG005, TH004
) AS PURTG2 ON PURTG2.TH004 = 材料品号 AND PURTG2.TG005 = 次供应商编号 AND 次供应商编号 != 主供应商编号
LEFT JOIN (
SELECT TH004, CAST(SUM(TH007) AS FLOAT) 非主次供应商待检量
FROM PURTG INNER JOIN PURTH ON TG001 = TH001 AND TG002 = TH002 INNER JOIN INVMB ON MB001 = TH004 AND TG005 NOT IN (MB032, INVMB.UDF06) WHERE TG013 = 'N' AND TH030 = 'N' GROUP BY TH004
) AS PURTG3 ON PURTG3.TH004 = 材料品号
ORDER BY 主供应商编号, 次供应商编号, 材料品号";
string a = sqlStr;
return(mssql.SQLselect(connYF, sqlStr));;
}