void EditLIB(ArrayList SQLString, SiftingDataEntity.OrderPickBodyEntity model) { StringBuilder strSql = new StringBuilder( ); strSql.Append("UPDATE JSKLIB SET "); strSql.AppendFormat("LIB003='{0}',", model.LIB003); strSql.AppendFormat("LIB004='{0}',", model.LIB004); strSql.AppendFormat("LIB005='{0}',", model.LIB005); strSql.AppendFormat("LIB006='{0}',", model.LIB006); strSql.AppendFormat("LIB008='{0}',", model.LIB008); strSql.AppendFormat("LIB019='{0}',", model.LIB019); strSql.AppendFormat("LIB982='{0}',", model.LIB982); strSql.AppendFormat("LIB983='{0}',", model.LIB983); strSql.AppendFormat("LIB984='{0}',", model.LIB984); strSql.AppendFormat("LIB960='{0}',", model.LIB960); strSql.AppendFormat("LIB007='{0}',", model.LIB007); strSql.AppendFormat("LIB020='{0}',", model.LIB020); strSql.AppendFormat("LIB021='{0}',", model.LIB021); strSql.AppendFormat("LIB022='{0}',", model.LIB022); strSql.AppendFormat("LIB023='{0}',", model.LIB023); strSql.AppendFormat("LIB024='{0}',", model.LIB024); strSql.AppendFormat("LIB961='{0}',", model.LIB961); strSql.AppendFormat("LIB964='{0}' ", model.LIB964); strSql.AppendFormat("WHERE LIB001='{0}' AND LIB002='{1}'", model.LIB001, model.LIB002); SQLString.Add(strSql.ToString( )); }
void AddPick(ArrayList SQLString, StringBuilder strSql, SiftingDataEntity.OrderPickBodyEntity model) { strSql = new StringBuilder( ); strSql.Append("INSERT INTO JSKLIB ("); strSql.Append("LIB001,LIB002,LIB003,LIB004,LIB005,LIB006,LIB008,LIB019,LIB982,LIB983,LIB984) "); strSql.Append("VALUES ("); strSql.AppendFormat("'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}') ", model.LIB001, model.LIB002, model.LIB003, model.LIB004, model.LIB005, model.LIB006, model.LIB008, model.LIB019, model.LIB982, model.LIB983, model.LIB984); //SqlParameter [ ] parameter = { // new SqlParameter("@LIB001",SqlDbType.NVarChar), // new SqlParameter("@LIB002",SqlDbType.NVarChar), // new SqlParameter("@LIB003",SqlDbType.NVarChar), // new SqlParameter("@LIB004",SqlDbType.NVarChar), // new SqlParameter("@LIB005",SqlDbType.NVarChar), // new SqlParameter("@LIB006",SqlDbType.NVarChar), // new SqlParameter("@LIB008",SqlDbType.Decimal), // new SqlParameter("@LIB019",SqlDbType.NVarChar), // new SqlParameter("@LIB982",SqlDbType.Decimal), // new SqlParameter("@LIB983",SqlDbType.Decimal) //}; //parameter [ 0 ] . Value = model . LIB001; //parameter [ 1 ] . Value = model . LIB002; //parameter [ 2 ] . Value = model . LIB003; //parameter [ 3 ] . Value = model . LIB004; //parameter [ 4 ] . Value = model . LIB005; //parameter [ 5 ] . Value = model . LIB006; //parameter [ 6 ] . Value = model . LIB008; //parameter [ 7 ] . Value = model . LIB019; //parameter [ 8 ] . Value = model . LIB982; //parameter [ 9 ] . Value = model . LIB983; SQLString.Add(strSql.ToString( )); }
void AddPick(ArrayList SQLString, StringBuilder strSql, SiftingDataEntity.OrderPickBodyEntity model) { strSql = new StringBuilder( ); strSql.Append("INSERT INTO JSKLIB ("); strSql.Append("LIB001,LIB002,LIB003,LIB004,LIB005,LIB006,LIB008,LIB019,LIB982,LIB983,LIB984,LIB960,LIB007,LIB020,LIB021,LIB022,LIB023,LIB024) "); strSql.Append("VALUES ("); strSql.AppendFormat("'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}') ", model.LIB001, model.LIB002, model.LIB003, model.LIB004, model.LIB005, model.LIB006, model.LIB008, model.LIB019, model.LIB982, model.LIB983, model.LIB984, model.LIB960, model.LIB007, model.LIB020, model.LIB021, model.LIB022, model.LIB023, model.LIB024); SQLString.Add(strSql.ToString( )); }
/// <summary> /// 生成领料单 不覆盖上次的 /// </summary> /// <param name="oddNum"></param> /// <param name="num"></param> /// <returns></returns> public int SavePickOne(string oddNum, string num, string piNum, string numOfOrder) { ArrayList SQLString = new ArrayList( ); StringBuilder strSql = new StringBuilder( ); SiftingDataEntity.OrderPickBodyEntity _body = new SiftingDataEntity.OrderPickBodyEntity( ); //_body . LIB961 = piNum; //_body . LIB964 = num; DataTable table; SiftingDataEntity.OrderPickEntity _header = new SiftingDataEntity.OrderPickEntity( ); _header.LIA004 = "C01"; _header.LIA005 = "004"; _header.LIA960 = oddNum + "-" + numOfOrder; _header.LIA964 = num; _header.LIA007 = "F"; _header.LIA013 = "1"; _header.LIA963 = piNum; strSql = new StringBuilder( ); strSql.AppendFormat("SELECT DEL013 LIA962 FROM QIXDEL WHERE DEL017='{0}' AND DEL016='{1}' AND DEL018='{2}'", oddNum, num, numOfOrder); table = SqlHelper.ExecuteDataTable(strSql.ToString( )); if (table == null || table.Rows.Count < 1) { _header.LIA011 = string.Empty; } else { _header.LIA011 = table.Rows [0] ["LIA962"].ToString( ); } strSql = new StringBuilder( ); //strSql . AppendFormat ( "SELECT DEL001 LIB003,DEL002 LIB004,DEL003 LIB024,DEL004 LIB005,DEL006 LIB006,DEL005 LIB019,DEL007*DEL008+DEL009 LIB008,DEL007 LIB982,DEL008 LIB983,DEL009 LIB984,DEL012 LIB960,IBB004 LIB964,IBB961 LIB961 FROM QIXDEL A INNER JOIN DCSIBB B ON A.DEL017=B.IBB001 WHERE DEL017='{0}' AND DEL016='{1}'" ,oddNum ,num ); strSql.AppendFormat("SELECT DEL001 LIB003,DEL002 LIB004,DEL003 LIB024,DEL004 LIB005,DEL006 LIB006,DEL005 LIB019,DEL007*DEL008+DEL009 LIB008,DEL007 LIB982,DEL008 LIB983,DEL009 LIB984,DEL012 LIB960 FROM QIXDEL WHERE DEL017='{0}' AND DEL016='{1}' AND DEL018='{2}'", oddNum, num, numOfOrder); table = SqlHelper.ExecuteDataTable(strSql.ToString( )); if (table == null || table.Rows.Count < 1) { //表示本地没有数据 return(-1); } DataTable tableOne; strSql = new StringBuilder( ); strSql.AppendFormat("SELECT COUNT(1) FROM JSKLIA WHERE LIA960='{0}' AND LIA964='{1}'", oddNum + "-" + numOfOrder, num); if (SqlHelper.Exists(strSql.ToString( ))) { return(-2); } strSql = new StringBuilder( ); strSql.AppendFormat("SELECT LIA001 FROM JSKLIA WHERE LIA960='{0}' AND LIA964='{1}'", oddNum + "-" + numOfOrder, num); tableOne = SqlHelper.ExecuteDataTable(strSql.ToString( )); if (!SqlHelper.Exists(strSql.ToString( ))) { _header.LIA001 = getOddNumPick( ); AddLIA(SQLString, _header); for (int i = 0; i < table.Rows.Count; i++) { _body.LIB001 = _header.LIA001; _body.LIB002 = (i + 1).ToString( ).PadLeft(3, '0'); _body.LIB003 = table.Rows [i] ["LIB003"].ToString( ); _body.LIB004 = table.Rows [i] ["LIB004"].ToString( ); _body.LIB005 = table.Rows [i] ["LIB005"].ToString( ); _body.LIB006 = table.Rows [i] ["LIB006"].ToString( ); _body.LIB007 = -1; _body.LIB008 = string.IsNullOrEmpty(table.Rows [i] ["LIB008"].ToString( )) == true ? 0 : Convert.ToDecimal(table.Rows [i] ["LIB008"].ToString( )); _body.LIB019 = table.Rows [i] ["LIB019"].ToString( ); _body.LIB020 = 1; _body.LIB021 = 1; _body.LIB022 = _body.LIB005; _body.LIB024 = table.Rows [i] ["LIB024"].ToString( ); _body.LIB023 = _body.LIB008; _body.LIB960 = table.Rows [i] ["LIB960"].ToString( ); //_body . LIB961 = table . Rows [ i ] [ "LIB961" ] . ToString ( ); //_body . LIB964 = table . Rows [ i ] [ "LIB964" ] . ToString ( ); _body.LIB984 = string.IsNullOrEmpty(table.Rows [i] ["LIB984"].ToString( )) == true ? 0 : Convert.ToDecimal(table.Rows [i] ["LIB984"].ToString( )); _body.LIB982 = string.IsNullOrEmpty(table.Rows [i] ["LIB982"].ToString( )) == true ? 0 : Convert.ToDecimal(table.Rows [i] ["LIB982"].ToString( )); _body.LIB983 = string.IsNullOrEmpty(table.Rows [i] ["LIB983"].ToString( )) == true ? 0 : Convert.ToDecimal(table.Rows [i] ["LIB983"].ToString( )); AddPick(SQLString, strSql, _body); } } else { return(-2); } if (SqlHelper.ExecuteSqlTran(SQLString)) { return(1); } else { return(2); } }
/// <summary> /// 生成领料单 第二次覆盖上次的 /// </summary> /// <param name="oddNum"></param> /// <returns></returns> public int SavePick(string oddNum, string num) { ArrayList SQLString = new ArrayList( ); StringBuilder strSql = new StringBuilder( ); DataTable table; SiftingDataEntity.OrderPickEntity _header = new SiftingDataEntity.OrderPickEntity( ); _header.LIA004 = "C01"; _header.LIA005 = "004"; _header.LIA960 = oddNum; _header.LIA964 = num; _header.LIA007 = "F"; _header.LIA013 = "1"; strSql = new StringBuilder( ); strSql.AppendFormat("SELECT DEL013 LIA962 FROM QIXDEL WHERE DEL017='{0}' AND DEL016='{1}'", oddNum, num); table = SqlHelper.ExecuteDataTable(strSql.ToString( )); if (table == null || table.Rows.Count < 1) { _header.LIA962 = string.Empty; } else { _header.LIA962 = table.Rows [0] ["LIA962"].ToString( ); } strSql = new StringBuilder( ); strSql.AppendFormat("SELECT DEL001 LIB003,DEL002 LIB004,DEL003 LIB024,DEL004 LIB005,DEL006 LIB006,DEL005 LIB019,DEL007*DEL008+DEL009 LIB008,DEL007 LIB982,DEL008 LIB983,DEL009 LIB984,DEL012 LIB960,IBB004 LIB964,IBB961 LIB961 FROM QIXDEL A INNER JOIN DCSIBB B ON A.DEL017=B.IBB001 WHERE DEL017='{0}' AND DEL016='{1}'", oddNum, num); table = SqlHelper.ExecuteDataTable(strSql.ToString( )); if (table == null || table.Rows.Count < 1) { //表示本地没有数据 return(-1); } DataTable tableOne; strSql = new StringBuilder( ); strSql.AppendFormat("SELECT COUNT(1) FROM JSKLIA WHERE LIA960='{0}' AND LIA964='{1}'", oddNum, num); tableOne = SqlHelper.ExecuteDataTable(strSql.ToString( )); if (!SqlHelper.Exists(strSql.ToString( ))) { _header.LIA001 = getOddNumPick( ); AddLIA(SQLString, _header); SiftingDataEntity.OrderPickBodyEntity _body = new SiftingDataEntity.OrderPickBodyEntity( ); for (int i = 0; i < table.Rows.Count; i++) { _body.LIB001 = _header.LIA001; _body.LIB002 = (i + 1).ToString( ).PadLeft(3, '0'); _body.LIB003 = table.Rows [i] ["LIB003"].ToString( ); _body.LIB004 = table.Rows [i] ["LIB004"].ToString( ); _body.LIB005 = table.Rows [i] ["LIB005"].ToString( ); _body.LIB006 = table.Rows [i] ["LIB006"].ToString( ); _body.LIB007 = -1; _body.LIB008 = string.IsNullOrEmpty(table.Rows [i] ["LIB008"].ToString( )) == true ? 0 : Convert.ToDecimal(table.Rows [i] ["LIB008"].ToString( )); _body.LIB019 = table.Rows [i] ["LIB019"].ToString( ); _body.LIB020 = 1; _body.LIB021 = 1; _body.LIB022 = _body.LIB005; _body.LIB024 = table.Rows [i] ["LIB024"].ToString( ); _body.LIB023 = _body.LIB008; _body.LIB960 = table.Rows [i] ["LIB960"].ToString( ); _body.LIB961 = table.Rows [i] ["LIB961"].ToString( ); _body.LIB964 = table.Rows [i] ["LIB964"].ToString( ); _body.LIB984 = string.IsNullOrEmpty(table.Rows [i] ["LIB984"].ToString( )) == true ? 0 : Convert.ToDecimal(table.Rows [i] ["LIB984"].ToString( )); _body.LIB982 = string.IsNullOrEmpty(table.Rows [i] ["LIB982"].ToString( )) == true ? 0 : Convert.ToDecimal(table.Rows [i] ["LIB982"].ToString( )); _body.LIB983 = string.IsNullOrEmpty(table.Rows [i] ["LIB983"].ToString( )) == true ? 0 : Convert.ToDecimal(table.Rows [i] ["LIB983"].ToString( )); AddPick(SQLString, strSql, _body); } } else { _header.LIA001 = tableOne.Rows [0] ["LIA001"].ToString( ); EditLIA(SQLString, _header); strSql = new StringBuilder( ); strSql.AppendFormat("SELECT LIB003,LIB006,LIB019,LIB002 FROM JSKLIB WHERE LIB001='{0}'", _header.LIA001); DataTable tableTwo = SqlHelper.ExecuteDataTable(strSql.ToString( )); SiftingDataEntity.OrderPickBodyEntity _body = new SiftingDataEntity.OrderPickBodyEntity( ); List <string> codeList = new List <string> ( ); string code = string.Empty; for (int i = 0; i < table.Rows.Count; i++) { _body.LIB001 = _header.LIA001; _body.LIB002 = (i + 1).ToString( ).PadLeft(3, '0'); _body.LIB003 = table.Rows [i] ["LIB003"].ToString( ); _body.LIB004 = table.Rows [i] ["LIB004"].ToString( ); _body.LIB005 = table.Rows [i] ["LIB005"].ToString( ); _body.LIB006 = table.Rows [i] ["LIB006"].ToString( ); _body.LIB007 = -1; _body.LIB008 = string.IsNullOrEmpty(table.Rows [i] ["LIB008"].ToString( )) == true ? 0 : Convert.ToDecimal(table.Rows [i] ["LIB008"].ToString( )); _body.LIB019 = table.Rows [i] ["LIB019"].ToString( ); _body.LIB020 = 1; _body.LIB021 = 1; _body.LIB022 = _body.LIB005; _body.LIB024 = table.Rows [i] ["LIB024"].ToString( ); _body.LIB023 = _body.LIB008; _body.LIB960 = table.Rows [i] ["LIB960"].ToString( ); _body.LIB961 = table.Rows [i] ["LIB961"].ToString( ); _body.LIB964 = table.Rows [i] ["LIB964"].ToString( ); _body.LIB984 = string.IsNullOrEmpty(table.Rows [i] ["LIB984"].ToString( )) == true ? 0 : Convert.ToDecimal(table.Rows [i] ["LIB984"].ToString( )); _body.LIB982 = string.IsNullOrEmpty(table.Rows [i] ["LIB982"].ToString( )) == true ? 0 : Convert.ToDecimal(table.Rows [i] ["LIB982"].ToString( )); _body.LIB983 = string.IsNullOrEmpty(table.Rows [i] ["LIB983"].ToString( )) == true ? 0 : Convert.ToDecimal(table.Rows [i] ["LIB983"].ToString( )); if (tableTwo != null && tableTwo.Rows.Count > 0) { if (tableTwo.Select("LIB003='" + _body.LIB003 + "' AND LIB006='" + _body.LIB006 + "' AND LIB019='" + _body.LIB019 + "'").Length > 0) { _body.LIB002 = tableTwo.Select("LIB003='" + _body.LIB003 + "' AND LIB006='" + _body.LIB006 + "' AND LIB019='" + _body.LIB019 + "'") [0] ["LIB002"].ToString( ); EditLIB(SQLString, _body); } else { _body.LIB002 = tableTwo.Compute("MAX(LIB002)", null).ToString( ); _body.LIB002 = (Convert.ToInt32(_body.LIB002) + 1).ToString( ).PadLeft(3, '0'); if (codeList.Contains(_body.LIB002)) { code = codeList.Max( ); } else { code = string.Empty; } if (code != string.Empty && Convert.ToInt32(code) > Convert.ToInt32(_body.LIB002)) { _body.LIB002 = (Convert.ToInt32(code) + 1).ToString( ).PadLeft(3, '0'); } AddPick(SQLString, strSql, _body); } } else { AddPick(SQLString, strSql, _body); } codeList.Add(_body.LIB002); } } if (SqlHelper.ExecuteSqlTran(SQLString)) { return(1); } else { return(2); } }
/// <summary> /// 生成领料单 /// </summary> /// <param name="oddNum"></param> /// <returns></returns> public int SavePick(string oddNum) { ArrayList SQLString = new ArrayList( ); StringBuilder strSql = new StringBuilder( ); strSql.AppendFormat("SELECT COUNT(1) FROM JSKLIA WHERE LIA960='{0}'", oddNum); if (SqlHelper.Exists(strSql.ToString( ))) { //已经存在领料单,不允许写入 return(-2); } SiftingDataEntity.OrderPickEntity _header = new SiftingDataEntity.OrderPickEntity( ); _header.LIA001 = getOddNumPick( ); _header.LIA004 = "C01"; _header.LIA005 = "004"; _header.LIA960 = oddNum; strSql = new StringBuilder( ); strSql.Append("INSERT INTO JSKLIA ("); strSql.Append("LIA001,LIA003,LIA004,LIA005,LIA012,LIA960) "); strSql.Append("VALUES ("); strSql.AppendFormat("'{0}','{3}','{1}','{4}','F','{2}')", _header.LIA001, _header.LIA004, _header.LIA960, getTime( ).ToString("yyyyMMdd"), _header.LIA005); SQLString.Add(strSql.ToString( )); strSql = new StringBuilder( ); strSql.AppendFormat("SELECT DEL001 LIB003,DEL002 LIB004,DEL004 LIB005,DEL006 LIB006,DEL005 LIB019,DEL007*DEL008+DEL009 LIB008,DEL007 LIB982,DEL008 LIB983,DEL009 LIB984 FROM QIXDEL WHERE DEL011='{0}'", oddNum); DataTable table = SqlHelper.ExecuteDataTable(strSql.ToString( )); if (table == null || table.Rows.Count < 1) { //表示本地没有数据 return(-1); } SiftingDataEntity.OrderPickBodyEntity _body = new SiftingDataEntity.OrderPickBodyEntity( ); for (int i = 0; i < table.Rows.Count; i++) { _body.LIB001 = _header.LIA001; _body.LIB002 = (i + 1).ToString( ).PadLeft(3, '0'); _body.LIB003 = table.Rows [i] ["LIB003"].ToString( ); _body.LIB004 = table.Rows [i] ["LIB004"].ToString( ); _body.LIB005 = table.Rows [i] ["LIB005"].ToString( ); _body.LIB006 = table.Rows [i] ["LIB006"].ToString( ); _body.LIB019 = table.Rows [i] ["LIB019"].ToString( ); _body.LIB984 = string.IsNullOrEmpty(table.Rows [i] ["LIB984"].ToString( )) == true ? 0 : Convert.ToDecimal(table.Rows [i] ["LIB984"].ToString( )); _body.LIB008 = string.IsNullOrEmpty(table.Rows [i] ["LIB008"].ToString( )) == true ? 0 : Convert.ToDecimal(table.Rows [i] ["LIB008"].ToString( )); _body.LIB982 = string.IsNullOrEmpty(table.Rows [i] ["LIB982"].ToString( )) == true ? 0 : Convert.ToDecimal(table.Rows [i] ["LIB982"].ToString( )); _body.LIB983 = string.IsNullOrEmpty(table.Rows [i] ["LIB983"].ToString( )) == true ? 0 : Convert.ToDecimal(table.Rows [i] ["LIB983"].ToString( )); AddPick(SQLString, strSql, _body); } if (SqlHelper.ExecuteSqlTran(SQLString)) { return(1); } else { return(2); } }