private void PrintWork() //销货单打印的主方法，定时器溢出后调用
{
Report report = new Report();
string sqlstr = @"SELECT TOP 1 TG001, TG002, MD_No, PrintType, PrintId FROM ROBOT_TEST.dbo.PrintData
INNER JOIN ROBOT_TEST.dbo.SplitTypeCode ON TG001 = OutType
WHERE STATUSS = 0 AND XhOutFlag = 1 AND PrintFlag = 0 AND PrintingFlag = 0
ORDER BY Create_Date ";
DataTable dt = mssql.SQLselect(connMD, sqlstr);
if (dt != null)
{
string tg001c = dt.Rows[0][0].ToString();
string tg002c = dt.Rows[0][1].ToString();
string md_noc = dt.Rows[0][2].ToString();
string printFileName = dt.Rows[0][3].ToString();
string printIdc = dt.Rows[0][4].ToString();
mssql.SQLexcute(connMD, string.Format(" UPDATE ROBOT_TEST.dbo.PrintData SET PrintingFlag = 1 WHERE PrintId = {0}",
printIdc));
report.LoadFromString(getfrx(printFileName));
report.SetParameterValue("@TG001", tg001c);
report.SetParameterValue("@TG002", tg002c);
report.SetParameterValue("@PD_NO", md_noc);
report.SetParameterValue("@PRINT_ID", printIdc);
report.PrintSettings.Printer = printerName;
report.PrintSettings.ShowDialog = false;
report.Print();
mssql.SQLexcute(connMD, string.Format(" UPDATE ROBOT_TEST.dbo.PrintData SET PrintFlag = 1, PrintDate = getdate(), PrintingFlag = 0 WHERE PrintId = {0}",
printIdc));
}
}
/// <summary>
/// 请购单中单头单身审核码不对应的
/// </summary>
private void Fix1()
{
string sqlStr = @"IF EXISTS(SELECT 1 FROM dbo.PURTA INNER JOIN dbo.PURTB ON TA001 = TB001 AND TA002 = TB002 WHERE TA007 <> TB025 AND TA003 >= '20200101')
BEGIN
UPDATE dbo.PURTB SET TB025 = TA007 FROM dbo.PURTA INNER JOIN dbo.PURTB ON TA001 = TB001 AND TA002 = TB002 WHERE TA007 <> TB025 AND TA003 >= '20200101'
END " ;
mssql.SQLexcute(connYF, sqlStr);
}
/// <summary>
/// 采购发票(委外)
/// </summary>
private void SetData1()
{
string sqlStr = @"UPDATE ACPTB SET TB013='1408'
FROM ACPTB
INNER JOIN ACPTA ON TB001=TA001 AND TB002=TA002
WHERE SUBSTRING(TA003,1,6)='{0}' AND (TB004='3' OR TB004='4')
AND EXISTS (SELECT 1 FROM ACMMJ WHERE TB037=MJ002 AND MJ003='3001' AND MJ004='Y' )
AND TB013 = '1407'";
mssql.SQLexcute(connYF, string.Format(sqlStr, dtp));
}
private void UptInfo()
{
string slqStr = @"UPDATE WG_DB.dbo.SC_PLAN SET
SC002 = X00, SC004 = X01, SC005 = X02, SC006 = X03, SC007 = X04, SC008 = X05,
SC009 = X06, SC010 = X07, SC011 = X08, SC012 = X09, SC015 = X10,
SC016 = X11, SC017 = X12, SC018 = X13, SC019 = X14, SC020 = X15, SC021 = X16,
SC022 = X17, SC024 = X18, SC025 = X19, SC026 = X20, SC027 = X21, SC028 = X22
FROM WG_DB.dbo.SC_PLAN
INNER JOIN (
SELECT
RTRIM(COPTD.TD001) + '-' + RTRIM(COPTD.TD002) + '-' + RTRIM(COPTD.TD003) AS DD,
(CASE WHEN TC004='0118' THEN '内销' ELSE '外销' END) AS X00,
RTRIM(COPMA.MA002) AS X01,
RTRIM(COPTC.TC015) AS X02,
RTRIM((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 X03,
RTRIM((CASE WHEN COPTD.TD013 = '' THEN '' WHEN COPTD.TD013 IS NULL THEN '' ELSE COPTD.TD013 END)) AS X04,
RTRIM((CASE WHEN COPTD.UDF03 = '' THEN '' WHEN COPTD.UDF03 IS NULL THEN '' ELSE COPTD.UDF03 END)) AS X05,
RTRIM(COPTD.UDF01) AS X06,
RTRIM(COPTD.TD005) AS X07,
RTRIM(COPTD.UDF08) AS X08,
RTRIM(COPTD.TD006) AS X09,
RTRIM(COPTD.TD053) AS X10,
RTRIM(COPTQ.TQ003) AS X11,
RTRIM((COPTQ.UDF07+COPTD.TD020)) AS X12,
RTRIM(COPTD.TD204) AS X13,
RTRIM(COPTC.TC035) AS X14,
RTRIM(CMSMV.MV002) AS X15,
RTRIM(COPTC.TC012) AS X16,
RTRIM(COPTD.TD014) AS X17,
RTRIM(COPTD.UDF05) AS X18,
RTRIM(COPTD.UDF10) AS X19,
RTRIM((CASE WHEN COPTC.UDF09='否' THEN '' ELSE '是' END)) AS X20,
RTRIM(COPTD.UDF12) AS X21,
RTRIM(COPTD.TD004) AS X22,
RTRIM(COPTQ.UDF07) AS X23
FROM COMFORT.dbo.COPTD AS COPTD
Left JOIN COMFORT.dbo.COPTC AS COPTC On COPTD.TD001=COPTC.TC001 and COPTD.TD002=COPTC.TC002
Left JOIN COMFORT.dbo.COPTQ AS COPTQ On COPTD.TD053=COPTQ.TQ002 and COPTD.TD004=COPTQ.TQ001
Left JOIN COMFORT.dbo.COPMA AS COPMA On COPTC.TC004=COPMA.MA001
Left JOIN COMFORT.dbo.CMSMV AS CMSMV On COPTC.TC006=CMSMV.MV001
LEFT JOIN COMFORT.dbo.INVMB AS INVMB ON COPTD.TD004=INVMB.MB001
Left JOIN COMFORT.dbo.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 COMFORT.dbo.COPTF
WHERE COPTD.TD001=COPTF.TF001 and COPTD.TD002=COPTF.TF002 and COPTD.TD003=COPTF.TF104)
LEFT JOIN COMFORT.dbo.CMSME AS CMSME ON CMSME.ME001 = INVMB.MB445
) AS A ON SC001 = DD
WHERE (SC028 IS NULL OR SC028 ='') ";
mssql.SQLexcute(connWG, slqStr);
}
private void Upload()
{
string sql = "INSERT INTO JH_LYXA "
+ "(COMPANY, CREATOR, USR_GROUP, CREATE_DATE, FLAG, JHXA001, JHXA002, JHXA003, "
+ "JHXA004, JHXA005, JHXA007, JHXA008, JHXA009, "
+ "JHXA011, JHXA012, JHXA013, JHXA014, JHXA015, ID, UDF02) "
+ "VALUES('COMFORT', '{0}', '{1}', '{2}', 1, '{3}', '{4}', '{5}', "
+ "'{6}', '{7}', '{8}', '{9}', '{10}', 'N', 'N', "
+ "'{11}', '********************', '{12}', '{13}', '{14}')";
string flowId = GetFlowID();
string Time = GetTime();
int Count = dgvMain.RowCount;
for (int Index = 0; Index < Count; Index++)
{
string JHXA001 = TypeID;
string JHXA002 = dgvMain.Rows[0].Cells["批号"].Value.ToString();
string JHXA003 = dgvMain.Rows[0].Cells["仓库"].Value.ToString();
string JHXA004 = GetDate();
string JHXA007 = dgvMain.Rows[0].Cells["品号"].Value.ToString();
string JHXA008 = "";
string JHXA009 = dgvMain.Rows[0].Cells["数量"].Value.ToString();
string JHXA013 = dgvMain.Rows[0].Cells["送货单号"].Value.ToString();
string JHXA015 = dgvMain.Rows[0].Cells["供应商"].Value.ToString();
string ID = (Index + 1).ToString();
string UDF02 = dateTimePicker2.Value.ToString("yyyyMMdd");
string slqStr = string.Format(sql, userId, userGroup, Time, JHXA001, JHXA002, JHXA003,
JHXA004, flowId, JHXA007, JHXA008, JHXA009, JHXA013, JHXA015, ID, UDF02);
mssql.SQLexcute(connYF, slqStr);
dgvMain.Rows.Remove(dgvMain.Rows[0]);
}
string tell = "\n\r处理流水号为：" + flowId;
string getBackStr = "";
try
{
getBackStr = createPurtg.HandelDef(flowId);
}
catch (Exception e)
{
Msg.ShowErr(e.ToString());
}
if (getBackStr != null)
{
tell += "\n\r进货单号为：" + getBackStr;
}
else
{
tell += "\n\r处理失败，请把记下流水处理号并联系咨询部。";
}
if (MessageBox.Show(tell, "提示", MessageBoxButtons.OK) == DialogResult.OK)
{
MsgFlag = true;
送货单号T.SelectAll();
送货单号T.Select();
panel_Last.Enabled = false;
}
}
private void Work()
{
string sqlStr = @"
DECLARE @NYR VARCHAR(20)
DECLARE @XH INT
DECLARE @JOBID VARCHAR(20)
DECLARE @SUBID VARCHAR(20)
SELECT @NYR = CONVERT(VARCHAR(20), GETDATE(), 112)
IF NOT EXISTS(SELECT * FROM [DSCSYS].[dbo].[JOBQUEUE] WHERE JOBNAME = 'BOMB05' AND STATUS IN ('P', 'N'))
BEGIN
IF EXISTS(SELECT * FROM [DSCSYS].[dbo].[JOBQUEUE] WHERE SUBSTRING(JOBID, 1, 8) = @NYR )
BEGIN
SELECT @XH = CONVERT(INT, SUBSTRING(MAX(JOBID), 9, 6))
FROM [DSCSYS].[dbo].[JOBQUEUE] WHERE SUBSTRING(JOBID, 1, 8) = @NYR
END
ELSE
BEGIN
SET @XH = 0
END
SELECT @JOBID = @NYR + RIGHT('000000' + CAST(@XH + 1 AS VARCHAR(6)), 6)
SELECT @SUBID = @NYR + '0001'
INSERT INTO [DSCSYS].[dbo].[JOBQUEUE] ([JOBID], [SUBID], [COMPANYID], [USERID], [USEDALIAS], [JOBNAME], [EXTNAME], [COMPROGID], [JOBOPTION], [GENTYPE], [GENSTATUS],
[PRIORITY], [STATUS], [PROGRESS], [DTREQUEST], [DTRECEIVE], [DTSCHEDULE], [DTSTART], [DTFINISH], [RESULT], [STYLE], [PROCESSER], [FLAG], [NOTIFY])
VALUES (@JOBID, @SUBID, 'COMFORT', 'Robot', 'COMFORT', 'BOMB05', '', 'BOMB05S.Class1',
0x44532056415249414E54202030313030180100000C2000000100000000000000010000000C2000000100000001000000030000000C20000001000000000000000100000008000000060000000990E9623B4EF64EC154F753080000000400000073007000300031000C20000001000000000000000100000008000000070000001F7510624C006F006700876563680800000006000000630068006B004C006F0067000C20000001000000000000000100000008000000040000005C4F1A4EE5651F67080000000B000000650064005000720069006E00740044006100740065000C2000000100000001000000030000000C200000010000000000000002000000080000000100000004000800000000000000080000000000000008000000010000004E000800000000000000,
1, 1, 3, 'N', NULL, GETDATE(), GETDATE(), GETDATE(), NULL, NULL, NULL, 'B', '', 1, '')
END";
mssql.SQLexcute(connYF, sqlStr);
}
/// <summary>
/// 替换客户配置中的勾选项
/// </summary>
/// <param name="tq001">成品品号</param>
/// <param name="tq002">客户配置名称</param>
/// <param name="tr003">上阶层级码</param>
/// <param name="wlnoOld">原品号</param>
/// <param name="wlnoNew">替换品号</param>
private void ReplaceWlno(string tq001, string tq002, string tr003, string wlnoOld, string wlnoNew, bool work)
{
string slqStr = @"UPDATE COPTR SET TR017 = '{4}' WHERE TR001='{0}' AND TR002='{1}' AND SUBSTRING(TR003, 1, LEN(TR003)-3)='{2}' AND TR009='{3}' ";
string sql1 = string.Format(slqStr, tq001, tq002, tr003, wlnoNew, "Y");
string sql2 = string.Format(slqStr, tq001, tq002, tr003, wlnoOld, "N");
LogAppend(string.Format("4. Replace Wlno: {0}", sql1));
if (work)
{
mssql.SQLexcute(connYF, sql1);
}
LogAppend(string.Format("4. Replace Wlno: {0}", sql2));
if (work)
{
mssql.SQLexcute(connYF, sql2);
}
}
private void Upload()
{
string sql = "INSERT INTO XH_LYXA "
+ "(COMPANY, CREATOR, USR_GROUP, CREATE_DATE, FLAG, XHXA001, XHXA002, XHXA003, "
+ "XHXA004, XHXA005, XHXA007, XHXA008, XHXA009, "
+ "XHXA011, XHXA012, XHXA013, XHXA014, XHXA015, ID) "
+ "VALUES('COMFORT3', '{0}', '{1}', '{2}', 1, '{3}', '{4}', '{5}', "
+ "'{6}', '{7}', '{8}', '{9}', '{10}', 'N', 'N', "
+ "'{11}', '********************', '{12}', '{13}')";
string flowId = GetFlowID();
string Time = GetTime();
int Count = DataGridView_List.RowCount;
for (int Index = 0; Index < Count; Index++)
{
string JHXA001 = TypeID;
string JHXA002 = DataGridView_List.Rows[0].Cells[6].Value.ToString();
string JHXA003 = DataGridView_List.Rows[0].Cells[5].Value.ToString();
string JHXA004 = GetDate();
string JHXA007 = DataGridView_List.Rows[0].Cells[1].Value.ToString();
string JHXA008 = "";
string JHXA009 = DataGridView_List.Rows[0].Cells[4].Value.ToString();
string JHXA013 = "";
string JHXA015 = "";
string ID = (Index + 1).ToString();
string sqlstr = string.Format(sql, LoginUid, LoginUserGroup, Time, JHXA001, JHXA002, JHXA003,
JHXA004, flowId, JHXA007, JHXA008, JHXA009, JHXA013, JHXA015, ID);
mssql.SQLexcute(connStr, sqlstr);
DataGridView_List.Rows.Remove(DataGridView_List.Rows[0]);
}
string tell = "\n\r处理流水号为：" + flowId;
string sqlstrEXEC = @"EXEC dbo.P_COPTG_CREATE_WORK_WG '{0}'";
DataTable dt = mssql.SQLselect(connStr, string.Format(sqlstrEXEC, flowId));
if (dt != null)
{
if (dt.Rows[0][0].ToString() != "")
{
tell += "\n\r销货单号为：" + dt.Rows[0][0].ToString();
}
if (dt.Rows[0][1].ToString() != "")
{
tell += "\n\r发生错误，信息为：" + dt.Rows[0][1].ToString();
}
}
if (MessageBox.Show(tell, "提示", MessageBoxButtons.OK) == DialogResult.OK)
{
MsgFlag = true;
panel_Last.Enabled = false;
}
}
private void Upload()
{
string sql = @"INSERT INTO DD_LYXA
(COMPANY, CREATOR, USR_GROUP, CREATE_DATE, FLAG, DDXA001, DDXA002, DDXA003, DDXA004,
DDXA005, DDXA006, DDXA007, DDXA008, DDXA009, DDXA010, DDXA011, DDXA012, DDXA013,
DDXA014, DDXA015, ID)
VALUES('COMFORT3', '{0}', '{1}', '{2}', 1, '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}',
'{10}', '{11}', '{12}', 'N', 'N', '{13}', '{14}', '{15}', '{16}')";
string flowId = GetFlowID();
string Time = GetTime();
int Count = dgvMain.RowCount;
for (int Index = 0; Index < Count; Index++)
{
string XA001 = dgvMain.Rows[Index].Cells["单别"].Value.ToString();
string XA002 = dgvMain.Rows[Index].Cells["客户编号"].Value.ToString();
string XA003 = dgvMain.Rows[Index].Cells["仓库"].Value.ToString();
string XA004 = dgvMain.Rows[Index].Cells["订单日期"].Value.ToString();
string XA006 = dgvMain.Rows[Index].Cells["采购单号"].Value.ToString();
string XA007 = dgvMain.Rows[Index].Cells["品号"].Value.ToString();
string XA008 = dgvMain.Rows[Index].Cells["预交货日"].Value.ToString();
string XA009 = dgvMain.Rows[Index].Cells["采购数量"].Value.ToString();
string XA010 = dgvMain.Rows[Index].Cells["生产单号"].Value.ToString();
string XA013 = dgvMain.Rows[Index].Cells["单身备注"].Value.ToString();
string XA014 = "";
string XA015 = "";
string ID = (Index + 1).ToString();
string sqlstr = string.Format(sql, LoginUid, LoginUserGroup, Time, XA001, XA002, XA003,
XA004, flowId, XA006, XA007, XA008, XA009, XA010, XA013, XA014, XA015, ID);
mssql.SQLexcute(connYF, sqlstr);
}
string tell = "\n\r处理流水号为：" + flowId;
string sqlstrEXEC = @"EXEC dbo.P_COPTC_CREATE_WORK_WG '{0}'";
DataTable dt = mssql.SQLselect(connYF, string.Format(sqlstrEXEC, flowId));
if (dt != null)
{
if (dt.Rows[0][0].ToString() != "")
{
tell += "\n\r订单单号为：" + dt.Rows[0][0].ToString();
}
if (dt.Rows[0][1].ToString() != "")
{
tell += "\n\r发生错误，信息为：" + dt.Rows[0][1].ToString();
}
}
if (MessageBox.Show(tell, "提示", MessageBoxButtons.OK) == DialogResult.OK)
{
}
}
private void contextMenuStrip_DgvMain_ItemClicked_GdConferm()
{
int rowIndex = DgvMain.CurrentCell.RowIndex;
string db = DgvMain.Rows[rowIndex].Cells["工单别"].Value.ToString();
string dh = DgvMain.Rows[rowIndex].Cells["工单号"].Value.ToString();
string slqStr1 = @"UPDATE dbo.MOCTA SET TA040=CONVERT(VARCHAR(8), GETDATE(), 112), TA013='U', FLAG=(FLAG%999)+1, TA041='{2}' "
+ "WHERE TA001='{0}' AND TA002='{1}'";
string slqStr2 = @"SELECT TB001, TB002, TB003, TB006 FROM dbo.MOCTB WHERE TB001='{0}' AND TB002='{1}' ";
string slqStr3 = @"UPDATE dbo.MOCTB SET TB018='Y', FLAG=(FLAG%999)+1 "
+ @"WHERE TB001='{0}' AND TB002='{1}' AND TB003='{2}' AND TB006='{3}' ";
string slqStr4 = @"UPDATE dbo.MOCTA SET TA013='Y', TA049='N' WHERE TA001='{0}' AND TA002='{1}' ";
if (mssql.SQLexcute(connYF, string.Format(slqStr1, db, dh, FormLogin.infObj.userId)) == 1) // 出错，未执行
{
Msg.ShowErr(string.Format("工单：{0}-{1} 未审核!", db, dh));
}
else
{
DataTable dt = mssql.SQLselect(connYF, string.Format(slqStr2, db, dh));
if (dt != null)
{
for (int index = 0; index < dt.Rows.Count; index++)
{
mssql.SQLexcute(connYF, string.Format(slqStr3, db, dh, dt.Rows[index]["TB003"].ToString().Trim(), dt.Rows[index]["TB006"].ToString()));
}
}
mssql.SQLexcute(connYF, string.Format(slqStr4, db, dh));
Msg.Show(string.Format("工单：{0}-{1} 已审核!", db, dh));
}
}
private void Work()
{
string sqlStr = @"
DELETE FROM SC_PLAN_Snapshot WHERE SC000 <= CONVERT(VARCHAR(8), DATEADD(DAY, -3, GETDATE()), 112)
INSERT INTO SC_PLAN_Snapshot(SC000, K_ID, SC001, SC003, SC013, SC023, SC028, SC029)
SELECT CONVERT(VARCHAR(8), GETDATE(), 112) SC000, K_ID, SC001, SC003, SC013, SC023, SC028, SC029 FROM SC_PLAN
WHERE 1=1
AND NOT EXISTS(SELECT 1 FROM SC_PLAN_Snapshot AS A WHERE A.K_ID = SC_PLAN.K_ID AND A.SC001 = SC_PLAN.SC001 AND A.SC000 = CONVERT(VARCHAR(8), GETDATE(), 112))
ORDER BY K_ID";
mssql.SQLexcute(connWG, sqlStr);
}
private void contextMenuStrip_DgvDone_ItemClicked_Delete()
{
string slqStr = @"DELETE FROM dbo.SC_PLAN WHERE K_ID = '{0}' AND SC001 = '{1}' AND SC003 = '{2}' AND SC013 = '{3}' AND SC023 = '{4}'";
int rowIndex = DgvDone.CurrentCell.RowIndex;
string msg = string.Format("是否确认删除当前行！\n\n序号：'{0}'\n生产单号：'{1}'\n上线日期'{2}'\n生产车间：'{3}'\n上线数量：'{4}'",
DgvDone.Rows[rowIndex].Cells["序号"].Value.ToString(),
DgvDone.Rows[rowIndex].Cells["生产单号"].Value.ToString(),
DgvDone.Rows[rowIndex].Cells["上线日期"].Value.ToString().Replace("-", ""),
DgvDone.Rows[rowIndex].Cells["生产车间"].Value.ToString(),
DgvDone.Rows[rowIndex].Cells["上线数量"].Value.ToString());
if (Msg.Show(msg) == DialogResult.OK)
{
SetScplanLog(DgvDone.Rows[rowIndex].Cells["序号"].Value.ToString(), "del");
mssql.SQLexcute(connWG, string.Format(slqStr, DgvDone.Rows[rowIndex].Cells["序号"].Value.ToString(),
DgvDone.Rows[rowIndex].Cells["生产单号"].Value.ToString(),
DgvDone.Rows[rowIndex].Cells["上线日期"].Value.ToString().Replace("-", ""),
DgvDone.Rows[rowIndex].Cells["上线数量"].Value.ToString(),
DgvDone.Rows[rowIndex].Cells["生产车间"].Value.ToString()
));
}
}
private void Insert()
{
string sql = "INSERT INTO TH_LYXA "
+ "(COMPANY, CREATOR, USR_GROUP, CREATE_DATE, FLAG, THXA001, THXA002, THXA003, "
+ "THXA004, THXA005, THXA007, THXA008, THXA009, "
+ "THXA011, THXA012, THXA013, THXA014, THXA015, ID) "
+ "VALUES('COMFORT', '{0}', '{1}', '{2}', 1, '{3}', '{4}', '{5}', "
+ "'{6}', '{7}', '{8}', '{9}', '{10}', 'N', 'N', "
+ "'{11}', '********************', '{12}', '{13}')";
string flowId = GetFlowID();
string Time = GetTime();
int Count = DataGridView_List.RowCount;
for (int Index = 0; Index < Count; Index++)
{
string THXA001 = TypeID;
string THXA002 = DataGridView_List.Rows[0].Cells[7].Value.ToString();
string THXA003 = DataGridView_List.Rows[0].Cells[5].Value.ToString();
string THXA004 = GetDate();
string THXA007 = DataGridView_List.Rows[0].Cells[1].Value.ToString();
string THXA008 = DataGridView_List.Rows[0].Cells[9].Value.ToString();
string THXA009 = DataGridView_List.Rows[0].Cells[4].Value.ToString();
string THXA013 = DataGridView_List.Rows[0].Cells[8].Value.ToString();
string THXA015 = DataGridView_List.Rows[0].Cells[7].Value.ToString();
string ID = (Index + 1).ToString();
string slqStr = string.Format(sql, LoginUid, LoginUserGroup, Time, THXA001, THXA002, THXA003,
THXA004, flowId, THXA007, THXA008, THXA009, THXA013, THXA015, ID);
mssql.SQLexcute(connYF, slqStr);
DataGridView_List.Rows.Remove(DataGridView_List.Rows[0]);
}
string tell = "\n\r处理流水号为：" + flowId;
string getBackStr = createPurti.HandelDef(flowId);
if (getBackStr != null)
{
tell += "\n退货单号为：" + getBackStr;
}
else
{
tell += "\n\r处理失败，请把记下流水处理号并联系咨询部。";
}
if (MessageBox.Show(tell, "提示", MessageBoxButtons.OK) == DialogResult.OK)
{
MsgFlag = true;
送货单号T.SelectAll();
送货单号T.Select();
panel_Last.Enabled = false;
}
}
private void MoctcIns()
{
infObjLL.tradeMode = infObjLL.gdDtTmp.Rows[0]["贸易方式"].ToString();
infObjLL.center = infObjLL.gdDtTmp.Rows[0]["工作中心"].ToString();
infObjLL.dpt = infObjLL.gdDtTmp.Rows[0]["部门编号"].ToString();
string slqStr = "INSERT INTO MOCTC(COMPANY, CREATOR, CREATE_DATE, FLAG, TC001, TC002, TC003, TC004, TC005, TC006, TC007, TC008, TC009, TC010, "
+ "TC011, TC012, TC013, TC014, TC015, TC016, TC017, TC018, TC019, TC020, TC021, TC022, TC023, TC024, TC025, TC026, TC027, TC028, "
+ "TC029, TC030, TC031, UDF01, UDF04, UDF02, UDF03, UDF09) "
+ "VALUES('COMFORT', '{0}', dbo.f_getTime(1), 1, '{1}', '{2}', LEFT(dbo.f_getTime(1), 8), '01', '{3}', '', '', '54', 'N', 0, 'N', '1', "
+ "'Y', LEFT(dbo.f_getTime(1), 8), '', 'N', 0, '0', '', '', '{4}', '', '', NULL, 0, 0, 0, '', 0, 0, '', '{5}', '{6}', '{7}', '{8}', '{9}')";
GetDh();
mssql.SQLexcute(infObjLL.connYF, string.Format(slqStr, infObjLL.userId, infObjLL.db, infObjLL.dh, infObjLL.center, infObjLL.dpt, infObjLL.order, infObjLL.tradeMode, infObjLL.orderNum, infObjLL.cpName, infObjLL.GroupList.Replace("\'", "")));
}
private void BtnSave_Click(object sender, EventArgs e)
{
string slqStr = @"UPDATE dbo.SC_PLAN_DPT_TYPE SET Valid = '{2}', ERPDpt = '{3}', DptWorkTime = '{4}' WHERE Type = '{0}' AND Dpt = '{1}' ";
for (int rowIndex = 0; rowIndex < showDt.Rows.Count; rowIndex++)
{
mssql.SQLexcute(connWG, string.Format(slqStr, DgvMain.Rows[rowIndex].Cells["类型"].Value.ToString().Replace("导入", "In").Replace("导出", "Out"),
DgvMain.Rows[rowIndex].Cells["排程部门"].Value.ToString(),
DgvMain.Rows[rowIndex].Cells["有效码"].Value.ToString().Replace("'True'", "1").Replace("'False'", "0"),
DgvMain.Rows[rowIndex].Cells["ERP部门编号"].Value.ToString(), DgvMain.Rows[rowIndex].Cells["部门总生产工时"].Value.ToString()));
}
ShowDgv();
UI();
}
private void DataUpdate()
{
string sqlStr = @"UPDATE COPTR SET TR017 = CB015
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";
mssql.SQLexcute(connYF, sqlStr);
}
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 void UpdOutputFlag()
{
string time = GetTime();
string slqStr = @"UPDATE SC_PLAN SET SC031 = '{1}' WHERE K_ID = '{0}' ";
string kidTmp = "";
if (kidList.Count > 0)
{
foreach (string kid in kidList)
{
if (kid != kidTmp)
{
kidTmp = kid;
mssql.SQLexcute(connWG, string.Format(slqStr, kid, time));
}
else
{
continue;
}
}
}
}
private void button1_Click(object sender, EventArgs e)
{
if (textBox2.Text == "")
{
Msg.ShowErr("排程部门不能为空");
}
else
{
string slqStr2 = @"SELECT 1 FROM dbo.SC_PLAN_DPT_TYPE WHERE Type = 'In' AND Dpt = '{0}' ";
if (mssql.SQLexist(connWG, string.Format(slqStr2, textBox2.Text)))
{
Msg.ShowErr("当前信息已存在，保存失败");
}
else
{
string slqStr = @"INSERT INTO dbo.SC_PLAN_DPT_TYPE (K_ID, [Type], Dpt, ERPDpt, Valid) VALUES((SELECT MAX(K_ID)+1 FROM dbo.SC_PLAN_DPT_TYPE WHERE [Type] = '{0}'), '{0}', '{1}', '{2}', 1) ";
mssql.SQLexcute(connWG, string.Format(slqStr, ComboBoxDptType.Text.Replace("导入", "In").Replace("导出", "Out"), textBox2.Text, textBox1.Text));
Msg.Show("已添加");
textBox2.Text = "";
}
}
}
/// <summary>
/// 收集工单工时
/// </summary>
private void SetData1()
{
string sqlStr = @"DECLARE @NIANYUE VARCHAR(6)
SET @NIANYUE='{0}'
DELETE FROM Comfortseating.dbo.CSTMB WHERE SUBSTRING(MB002,1,6)=@NIANYUE
INSERT INTO
Comfortseating.dbo.CSTMB(COMPANY,CREATOR,USR_GROUP,CREATE_DATE,MODIFIER,MODI_DATE,FLAG,
MB001,MB002,MB003,MB004,MB005,MB006,MB007,MB008,MB009,MB010,MB011,MB012,MB013,
UDF01,UDF02,UDF03,UDF04,UDF05,UDF06,UDF07,UDF08,UDF09,UDF10,UDF11,UDF12,UDF51,UDF52,
UDF53,UDF54,UDF55,UDF56,UDF57,UDF58,UDF59,UDF60,UDF61,UDF62 )
select 'comfortwx','DS','','','','',1,
MOCTA.TA021,CSTTA.TA002+'01',CSTTA.TA003,CSTTA.TA004,CSTTA.TA015,0,MOCTA.TA006,'','','',0,0,0,
'','','','','','','','','','','','',
0,0,0,0,0,0,0,0,0,0,0,0
from Comfortseating.dbo.CSTTA CSTTA
INNER JOIN Comfortseating.dbo.MOCTA MOCTA ON CSTTA.TA003=MOCTA.TA001 AND CSTTA.TA004=MOCTA.TA002
WHERE CSTTA.TA002=@NIANYUE ";
mssql.SQLexcute(connSW, string.Format(sqlStr, dtp));
}
private void Work()
{
string sqlStr = @"IF EXISTS (SELECT* FROM DSCSYS.dbo.JOBQUEUE WHERE JOBNAME IN ('COPAB02', 'COPAB01', 'LRPMB01', 'LRPB01', 'LRPMB02', 'LRPB02') AND STATUS IN ('P', 'N'))
BEGIN
DELETE FROM [COMFORT].[dbo].[COPTR] WHERE TR003 like 'X%'
AND TR001 NOT LIKE '6%'
AND TR001 NOT LIKE '7%'
AND TR001 NOT LIKE '8%'
AND TR001 NOT LIKE '9%'
UPDATE COPTR SET TR003 = SUBSTRING(TR003, 2, LEN(TR003))
WHERE (TR001 LIKE '6%'
OR TR001 LIKE '7%'
OR TR001 LIKE '8%'
OR TR001 LIKE '9%')
AND TR003 LIKE 'X%'
END ";
mssql.SQLexcute(connYF, sqlStr);
}
/// <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 void Work()
{
string sqlStr = @"
UPDATE COMFORT.dbo.MOCTA
SET TA064 = MV004, TA021 = MD001
FROM COMFORT.dbo.MOCTA
INNER JOIN COMFORT.dbo.CMSMV ON MV001 = TA041
INNER JOIN COMFORT.dbo.CMSMD ON MD015 = MV004 AND MD001 IN ('1' ,'6' , '7')
WHERE 1=1
AND TA013 = 'Y'
AND TA011 NOT IN ('Y', 'y')
AND (TA064 != MV004 OR MD001 != TA021)
AND (MD001 IS NOT NULL OR RTRIM(MD001) != '')
AND (MV004 IS NOT NULL OR RTRIM(MV004) != '') ";
mssql.SQLexcute(connYF, sqlStr);
}
private void BoxSizeUpdate(string mb001, string mbu01, int mbu02)
{
string sqlStr = "update dbo.INVMB SET MBU01='{1}', MBU02={2} WHERE MB001 = '{0}' ";
mssql.SQLexcute(connYF, string.Format(sqlStr, mb001, mbu01, mbu02));
}
private void DeleteList(string wlno)
{
string sqlStr = "delete from BOMCB_List where CB001 = '{0}'";
mssql.SQLexcute(connYF, string.Format(sqlStr, wlno));
}
private void insertsql()
{
string slqStr = "";
string danhao = "";
int index;
if (dttmp != null)
{
danhao = dttmp.Rows[0][0].ToString().Trim() + "-" + dttmp.Rows[0][1].ToString().Trim();
string create_date = getTime();
string xa001 = "";
string xa002 = "";
string xa003 = "";
string xa009 = "";
string xa010 = "";
string xa011 = "";
string xa012 = "";
string xa013 = "";
string xa015 = "";
string xa017 = "";
string xa018 = "";
if (!checkLLXA007(xa007))
{
xa007 = getLLXA007();
}
for (index = 0; index < dttmp.Rows.Count; index++)
{
xa001 = dttmp.Rows[index][0].ToString().Trim();
xa002 = dttmp.Rows[index][1].ToString().Trim();
xa003 = dttmp.Rows[index][2].ToString().Trim();
xa009 = dttmp.Rows[index][12].ToString().Trim();
xa010 = dttmp.Rows[index][13].ToString().Trim();
xa011 = dttmp.Rows[index][9].ToString().Trim();
xa012 = dttmp.Rows[index][3].ToString().Trim();
xa013 = dttmp.Rows[index][7].ToString().Trim();
xa015 = dttmp.Rows[index][11].ToString().Trim();
xa017 = dttmp.Rows[index][6].ToString().Trim();
xa018 = dttmp.Rows[index][14].ToString().Trim();
slqStr += "INSERT INTO LL_LYXA (COMPANY, CREATOR, USR_GROUP, CREATE_DATE, LLXA001, LLXA002, LLXA003, LLXA007, LLXA009, LLXA010, LLXA011, LLXA012, LLXA013, LLXA015, LLXA017, LLXA018) VALUES ( 'COMFORT', "
+ "'" + userId + "', '" + userGroup + "', '" + create_date + "', '" + xa001 + "', '" + xa002 + "', '" + xa003 + "', '" + xa007 + "', '" + xa009 + "','" + xa010 + "', '" + xa011 + "', '" + xa012 + "','"
+ xa013 + "', '" + xa015 + "', '" + xa017 + "', '" + xa018 + "') ";
}
mssql.SQLexcute(connYF, slqStr);
DataGridView_List.DataSource = null;
Button_Upload.Enabled = false;
TextBox_Danhao.Text = "";
TextBox_Danhao.Select();
Lable_Danhao.Text = "";
label3.Text = "已上传" + index.ToString() + "条记录！";
dttmp = null;
MessageBox.Show("已上传" + index.ToString() + "条记录！", "");
}
else
{
Button_Upload.Enabled = false;
MessageBox.Show("", "错误");
}
}