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));
}