public bool GetPrintRecordListByPage(ref List <Barcode_Model> modelList, Barcode_Model model, ref DividPage page, UserInfo user, ref string strError) { if (page == null) { page = new DividPage(); } List <Barcode_Model> lstModel = new List <Barcode_Model>(); try { using (SqlDataReader dr = Common_DB.QueryByDividPage(ref page, "v_printrecord", GetFilterSql(model, user), "barcodetype, strbarcodetype, supcode, supname, vouchertype, strvouchertype, sum(printqty) printqty", "Order by SupCode, BarcodeType, VoucherType, sum(PrintQty) desc")) { while (dr.Read()) { lstModel.Add(GetModelFromDataReader(dr)); } } modelList = lstModel; return(true); } catch (Exception ex) { strError = ex.Message; return(false); } finally { } }
private void button31_Click(object sender, EventArgs e) { string json = localWebTest.GetCheckBarcodeForAndroid(7, "16031620001", false); BLL.PrintBarcode.Barcode_Model bar = BLL.JSONUtil.JSONHelper.JsonToObject <BLL.PrintBarcode.Barcode_Model>(json); MessageBox.Show("ad"); }
public bool GetTrayInfoByTrayIDForOutStock(Barcode_Model Barcode_Model, ref Tray_Model tray) { bool isResult = false; Tray_DB trayDB = new Tray_DB(); isResult = trayDB.GetTrayInfoByTrayID2(Barcode_Model, ref tray); return(isResult); }
private string GetFilterSql(Barcode_Model model, UserInfo user) { try { string strSql = ""; bool hadWhere = false; if (!string.IsNullOrEmpty(model.SUPCODE)) { strSql = Common_Func.AddWhereAnd(strSql, hadWhere); strSql += " (SUPCODE LIKE '%" + model.SUPCODE + "%' OR SUPNAME LIKE '%" + model.SUPCODE + "%') "; hadWhere = true; } if (model.BARCODETYPE >= 1) { strSql = Common_Func.AddWhereAnd(strSql, hadWhere); strSql += " BARCODETYPE = '" + model.BARCODETYPE + "' "; hadWhere = true; } if (!string.IsNullOrEmpty(model.VOUCHERTYPE)) { strSql = Common_Func.AddWhereAnd(strSql, hadWhere); strSql += " VOUCHERTYPE = '" + model.VOUCHERTYPE + "' "; hadWhere = true; } else { strSql = Common_Func.AddWhereAnd(strSql, hadWhere); strSql += " (VOUCHERTYPE = '10' or VOUCHERTYPE = '70') "; hadWhere = true; } if (model.StartTime != null) { strSql = Common_Func.AddWhereAnd(strSql, hadWhere); strSql += " PrintTime >= " + model.StartTime.ToDateTime().Date.ToSqlTimeString() + " "; hadWhere = true; } if (model.EndTime != null) { strSql = Common_Func.AddWhereAnd(strSql, hadWhere); strSql += " PrintTime <= " + model.EndTime.ToDateTime().AddDays(1).Date.ToSqlTimeString() + " "; hadWhere = true; } strSql += " group by barcodetype, strbarcodetype, supcode, supname, vouchertype, strvouchertype "; return(strSql); } catch { return(string.Empty); } }
public bool UpdateTrayInfo(Barcode_Model Barcode_Model) { bool isResult = false; Tray_DB trayDB = new Tray_DB(); string strErrMsg = string.Empty; isResult = trayDB.UpdateTrayInfo(Barcode_Model); return(isResult); }
public bool UpdateTrayInfo(string BarcodeInfo) { Barcode_Model barcodeMdl = new Barcode_Model(); barcodeMdl = JSONUtil.JSONHelper.JsonToObject <Barcode_Model>(BarcodeInfo); bool isResult = false; isResult = UpdateTrayInfo(barcodeMdl); return(isResult); }
public string UpdateTrayInfoPro(string BarcodeInfo) { Barcode_Model barcodeMdl = new Barcode_Model(); barcodeMdl = JSONUtil.JSONHelper.JsonToObject <Barcode_Model>(BarcodeInfo); bool isResult = false; Tray_DB trayDB = new Tray_DB(); string strErrMsg = null; isResult = trayDB.UpdateTrayInfoPro(barcodeMdl, ref strErrMsg); return(GetReturnJson(isResult, barcodeMdl, strErrMsg)); }
private Barcode_Model GetModelFromDataReader(SqlDataReader dr) { Barcode_Model model = new Barcode_Model(); model.BARCODETYPE = dr["BARCODETYPE"].ToDecimal(); model.StrBarcodeType = dr["StrBarcodeType"].ToDBString(); model.SUPCODE = dr["SUPCODE"].ToDBString(); model.SUPNAME = dr["SUPNAME"].ToDBString(); model.VOUCHERTYPE = dr["VOUCHERTYPE"].ToDBString(); model.StrVoucherType = dr["StrVoucherType"].ToDBString(); model.PRINTQTY = dr["PRINTQTY"].ToDecimal(); return(model); }
public bool GetTrayInfoByTrayID(Barcode_Model Barcode_Model, ref Tray_Model trayInfo) { bool IsResult = false; DataSet ds; string sql = null, errMsg = string.Empty; sql = string.Format(@"select TRAYID,TrayNO,qty,materialno,materialdesc,materialstd,serialno,rowno from T_TRAY JOIN T_OUTBARCODE ON T_TRAY.ID=T_OUTBARCODE.TRAYID where T_TRAY.ID={0}", Barcode_Model.TrayID); OperationSql.GetDataset(sql, out ds, out errMsg); if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { trayInfo = TOOL.DataTableToList.DataRowToModel <Tray_Model>(ds.Tables[0].Rows[0]); trayInfo.listDetails = new List <TrayDetails_Model>(); TrayDetails_Model model; foreach (DataRow item in ds.Tables[0].Rows) { TrayDetails_Model tdm = trayInfo.listDetails.Find(delegate(TrayDetails_Model v) { return(v.cinvcode.ToUpper().Equals(item["materialno"].ToString().ToUpper()) && (string.IsNullOrEmpty(item["rowno"].ToDBString()) || v.ROWNO.Equals(item["rowno"].ToDBString()))); }); if (tdm == null) { model = new TrayDetails_Model(); model.Qty = ObjectExtend.ToDouble(item["qty"]); model.cinvcode = item["materialno"].ToString(); model.cinvname = item["materialdesc"].ToString(); model.cinvstd = item["materialstd"].ToString(); model.ROWNO = item["rowno"].ToDBString(); model.listBarcode = new List <string>() { item["serialno"].ToString() }; trayInfo.listDetails.Add(model); } else { tdm.listBarcode.Add(item["serialno"].ToString()); tdm.Qty += ObjectExtend.ToDouble(item["qty"]); } } IsResult = true; } return(IsResult); }
public bool GetTrayInfoByTrayID2(Barcode_Model Barcode_Model, ref Tray_Model trayInfo) { bool IsResult = false; DataSet ds; string sql = null, errMsg = string.Empty; sql = string.Format(@"select voucherno,TRAYID,TrayNO,qty,materialno,materialdesc,materialstd,serialno,rowno from T_TRAY JOIN T_OUTBARCODE ON T_TRAY.ID=T_OUTBARCODE.TRAYID where T_TRAY.ID={0}", Barcode_Model.TrayID); OperationSql.GetDataset(sql, out ds, out errMsg); if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { ds.Tables[0].Columns.Add("socode", typeof(System.String)); ds.Tables[0].Columns.Add("sorowno", typeof(System.String)); //SqlDataReader sqlreader = null; //using (SqlConnection conOne = new SqlConnection(OperationSql.U8ConnStr)) //{ // conOne.Open(); // SqlCommand command = new SqlCommand(); // command.Connection = conOne; // foreach (DataRow item in ds.Tables[0].Rows) // { // sql = string.Format("select ISNULL(ordercode,'') socode,ISNULL(orderseq,'') sorowno from v_st_mom_orderdetail where mocode = '{0}' and MoSeq='{1}'", item["voucherno"].ToDBString(), item["rowno"].ToDBString()); // command.CommandText = sql; // sqlreader = command.ExecuteReader(); // if (sqlreader.Read()) // { // item["socode"] = sqlreader["socode"].ToDBString(); // item["sorowno"] = sqlreader["sorowno"].ToDBString(); // } // if (sqlreader != null && !sqlreader.IsClosed) // sqlreader.Close(); // } //} trayInfo = TOOL.DataTableToList.DataRowToModel <Tray_Model>(ds.Tables[0].Rows[0]); trayInfo.listDetails = new List <TrayDetails_Model>(); TrayDetails_Model model; foreach (DataRow item in ds.Tables[0].Rows) { TrayDetails_Model tdm = trayInfo.listDetails.Find(delegate(TrayDetails_Model v) { return(v.cinvcode.ToUpper().Equals(item["materialno"].ToString().ToUpper()) && (string.IsNullOrEmpty(item["sorowno"].ToDBString()) || item["sorowno"].ToDBString().Equals("0") || v.SOROWNO.Equals(item["sorowno"].ToDBString()))); }); if (tdm == null) { model = new TrayDetails_Model(); model.Qty = ObjectExtend.ToDouble(item["qty"]); model.cinvcode = item["materialno"].ToString(); model.cinvname = item["materialdesc"].ToString(); model.cinvstd = item["materialstd"].ToString(); model.SOROWNO = item["sorowno"].ToDBString(); model.listBarcode = new List <string>() { item["serialno"].ToString() }; trayInfo.listDetails.Add(model); } else { tdm.listBarcode.Add(item["serialno"].ToString()); tdm.Qty += ObjectExtend.ToDouble(item["qty"]); } } IsResult = true; } return(IsResult); }
public bool UpdateTrayInfoPro(Barcode_Model Barcode_Model, ref string strErrMsg) { bool IsResult = false; SqlCommand cmd = new SqlCommand(); SqlConnection conn = new SqlConnection(); SqlDataAdapter adp = new SqlDataAdapter(); conn.ConnectionString = OperationSql.connectionString; try { conn.Open(); } catch (Exception ex) { strErrMsg = ex.Message; return(IsResult); } SqlTransaction myTran; myTran = conn.BeginTransaction(); cmd.Connection = conn; cmd.Transaction = myTran; DataSet result = new DataSet(); string sql; object o; try { int trayID = 0; //托ID string trayNO = string.Empty; //托号 string productlineNo = Barcode_Model.cProductLineCode; //产线编码 if (Barcode_Model.tray_Model.TrayID == 0) //还没有托号记录则手动生成 { //设置要调用的存储过程的名称 cmd.CommandText = "P_GetNewSeqVal_SEQ_TRAYID"; //指定SqlCommand对象传给数据库的是存储过程的名称而不是sql语句 cmd.CommandType = CommandType.StoredProcedure; SqlParameter SQLid = cmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int)); //指定"@ID"为输出参数 SQLid.Direction = ParameterDirection.Output; //执行 cmd.ExecuteNonQuery(); trayID = Convert.ToInt32(cmd.Parameters["@ID"].Value); cmd.CommandText = "P_GetNewSeqVal_SEQ_TRAY"; //执行 cmd.ExecuteNonQuery(); trayNO = DateTime.Now.ToString("yyMMdd") + Convert.ToString(cmd.Parameters["@ID"].Value).PadLeft(4, '0'); cmd.Parameters.Clear(); cmd.CommandType = CommandType.Text; if (string.IsNullOrEmpty(Barcode_Model.cProductLineCode)) { sql = string.Format("insert into T_TRAY (ID,TrayNO,[createDt]) values ({0},'{1}',getdate())", trayID, trayNO); } else { sql = string.Format("insert into T_TRAY (ID,TrayNO,[ProductLineNo],[createDt]) values ({0},'{1}','{2}',getdate())", trayID, trayNO, productlineNo); } cmd.CommandText = sql; cmd.ExecuteNonQuery(); } else { sql = string.Format("select serialno,qty from T_OUTBARCODE where trayid={0}", Barcode_Model.tray_Model.TrayID); cmd.CommandText = sql; adp.SelectCommand = cmd; adp.Fill(result); //拆托 foreach (DataRow dr in result.Tables[0].Rows) { bool IsFound = false; foreach (var item in Barcode_Model.tray_Model.listDetails) { if (item.listBarcode.Contains(dr["serialno"].ToString())) { IsFound = true; break; } } if (!IsFound) { sql = string.Format("update T_OUTBARCODE set trayid=null,ProductLineNo=null where serialno='{0}';update T_TRAY SET TrayQty=ISNULL(TrayQty,0)-{2},[lastModifyDt]=getdate(),[lastModifyType]=-1 WHERE id={1}", dr["serialno"].ToString(), Barcode_Model.tray_Model.TrayID, dr["qty"].ToString()); cmd.CommandText = sql; cmd.ExecuteNonQuery(); } } } int lastModifyType = 0;//组 if (trayID == 0) { trayID = Barcode_Model.tray_Model.TrayID; lastModifyType = 1;//拼 } //拼托 foreach (var item in Barcode_Model.tray_Model.listDetails) { foreach (var detail in item.listBarcode) { sql = string.Format("select serialno from T_OUTBARCODE where serialno='{0}' and trayid={1}", detail, trayID); cmd.CommandText = sql; o = cmd.ExecuteScalar(); if (o == null || o == DBNull.Value || string.IsNullOrEmpty(o.ToString())) { sql = string.Format("select qty from T_OUTBARCODE where serialno='{0}'", detail); cmd.CommandText = sql; o = cmd.ExecuteScalar(); if (string.IsNullOrEmpty(productlineNo)) { sql = string.Format("update T_OUTBARCODE set trayid={1} where serialno='{0}';update T_TRAY SET TrayQty=ISNULL(TrayQty,0)+{2},[lastModifyDt]=getdate(),[lastModifyType]={3} WHERE id={1}", detail, trayID, o.ToString(), lastModifyType); } else { sql = string.Format("update T_OUTBARCODE set trayid={1},ProductLineNo='{3}' where serialno='{0}';update T_TRAY SET TrayQty=ISNULL(TrayQty,0)+{2},[lastModifyDt]=getdate(),[lastModifyType]={4} WHERE id={1}", detail, trayID, o.ToString(), productlineNo, lastModifyType); } cmd.CommandText = sql; cmd.ExecuteNonQuery(); } } } myTran.Commit(); return(true); } catch (Exception ex) { strErrMsg = ex.Message; myTran.Rollback(); return(false); } finally { conn.Dispose(); conn.Close(); } }
public bool GetTrayInfoByTrayIDForOutStock(Barcode_Model Barcode_Model, ref Tray_Model trayInfo) { bool IsResult = false; string connString = OperationSql.ERPConnStr; DataSet ds = new DataSet(); string errMsg = ""; SqlCommand view = new SqlCommand(); SqlCommand cmd = new SqlCommand(); SqlConnection conn = new SqlConnection(); SqlDataAdapter adp = new SqlDataAdapter(); conn.ConnectionString = connString; try { conn.Open(); } catch (Exception ex) { errMsg = ex.Message; return(false); } cmd.Connection = conn; string sqlHead = string.Format(@"select ISNULL(orderseq,'') sorowno,TRAYID,TrayNO,bar.qty,materialno,serialno FROM ITSV.Barcode.DBO.T_TRAY tray join ITSV.Barcode.DBO.T_OUTBARCODE bar ON tray.ID=bar.TRAYID left JOIN v_st_mom_orderdetail m ON m.MoSeq=bar.rowno and bar.voucherno=m.MoCode where 1=1 and tray.ID={0}", Barcode_Model.TrayID); string WMSdbserver = "", WMSdbuser = "", WMSdbpwd = ""; string tmp = OperationSql.connectionString; string tmp1 = ""; tmp1 = tmp.Substring(tmp.IndexOf("Data Source=")); WMSdbserver = tmp1.Substring(12, tmp1.IndexOf(";") - 12); tmp1 = tmp.Substring(tmp.IndexOf("UID=")); WMSdbuser = tmp1.Substring(4, tmp1.IndexOf(";") - 4); tmp1 = tmp.Substring(tmp.IndexOf("Pwd=")); WMSdbpwd = tmp1.Substring(4, tmp1.Length - 4); string CreatelinkServer = ""; CreatelinkServer = (@"if exists(select 1 from master.dbo.sysservers where srvname='ITSV') begin EXEC sp_dropserver 'ITSV','droplogins' end exec sp_addlinkedserver 'ITSV', '', 'SQLOLEDB ', '" + WMSdbserver + @"'; exec sp_addlinkedsrvlogin 'ITSV', 'false ',null, '" + WMSdbuser + "', '" + WMSdbpwd + @"' ;"); DataTable dt1 = new DataTable("head"); ds.Tables.Add(dt1); try { cmd.CommandText = CreatelinkServer; cmd.ExecuteNonQuery(); cmd.CommandText = sqlHead; adp.SelectCommand = cmd; adp.Fill(ds.Tables["head"]); if (ds.Tables["head"].Rows.Count < 1) { errMsg = "生产订单不存在或状态不正确!"; return(false); } if (ds != null && ds.Tables.Count > 0 && ds.Tables["head"].Rows.Count > 0) { trayInfo = TOOL.DataTableToList.DataRowToModel <Tray_Model>(ds.Tables[0].Rows[0]); trayInfo.listDetails = new List <TrayDetails_Model>(); TrayDetails_Model model; foreach (DataRow item in ds.Tables["head"].Rows) { TrayDetails_Model tdm = trayInfo.listDetails.Find(delegate(TrayDetails_Model v) { return(v.cinvcode.ToUpper().Equals(item["materialno"].ToString().ToUpper()) && (string.IsNullOrEmpty(item["sorowno"].ToDBString()) || item["sorowno"].ToDBString().Equals("0") || v.SOROWNO.Equals(item["sorowno"].ToDBString()))); }); if (tdm == null) { model = new TrayDetails_Model(); model.Qty = ObjectExtend.ToDouble(item["qty"]); model.cinvcode = item["materialno"].ToString(); model.SOROWNO = item["sorowno"].ToDBString(); model.listBarcode = new List <string>() { item["serialno"].ToString() }; trayInfo.listDetails.Add(model); } else { tdm.listBarcode.Add(item["serialno"].ToString()); tdm.Qty += ObjectExtend.ToDouble(item["qty"]); } IsResult = true; } } return(IsResult); } catch (Exception ex) { errMsg = ex.Message; return(false); } finally { conn.Close(); } }
private string GetReturnJson(bool bSucc, Barcode_Model Info, string strErrMsg) { Info.Status = bSucc == true ? "S" : "E"; Info.Message = strErrMsg; return(JSONUtil.JSONHelper.ObjectToJson <Barcode_Model>(Info)); }