/// <summary>
/// 整合追溯信息为Hashtable
/// </summary>
/// <returns></returns>
private Hashtable GetHashtable()
{
try
{
string sql = string.Format("SELECT * FROM (SELECT t.* FROM (SELECT b.*, m.tablename, m.fieldname FROM productinfo p, productbominfo b, materialfield m WHERE p.productcode = b.productcode AND p.producttype = b.producttype AND b.materialcode = m.materialcode AND m.tablename='{0}') t LEFT JOIN batchno n ON t.materialcode = n.materialcode) t WHERE productcode='{1}' AND producttype='{2}' AND tracetype in({3});", BaseVariable.ResultTableName, this.productModel.ProductCode, BaseVariable.DeviceEntity.ProductType, "'扫描追溯'");
DataTable table = LocalDbDAL.GetDataTable(sql);
if (table != null && table.Rows.Count > 0)
{
Hashtable HTSelect = MaterialBomDAL.GetList(table);
Hashtable HTResult = new Hashtable();
foreach (var item in RequestParam)
{
if (HTSelect.ContainsKey(item.Key))
{
MaterialBomMDL model = HTSelect[item.Key] as MaterialBomMDL;
model.BatchBarCode = item.Value;
HTResult.Add(item.Key, model);
}
}
return(HTResult);
}
return(null);
}
catch (Exception ex)
{
CLog.WriteErrLog(ex.Message);
return(null);
}
}
/// <summary>
/// 获取集合（带扩展的）
/// </summary>
/// <param name="table"></param>
/// <param name="ExtCount">扩展的数量</param>
/// <returns></returns>
public static Dictionary <string, MaterialBomMDL> GetList(DataTable table, int ExtCount)
{
Dictionary <string, MaterialBomMDL> HT = new Dictionary <string, MaterialBomMDL>();
MaterialBomMDL model = null;
foreach (DataRow row in table.Rows)
{
string MaterialCode = row["materialcode"].ToString();
model = GetModel(row, ExtCount);
HT.Add(MaterialCode, model);
}
return(HT);
}
/// <summary>
/// 获取集合（带扩展的）
/// </summary>
/// <param name="table"></param>
/// <param name="ExtCount">扩展的数量</param>
/// <returns></returns>
public static Hashtable GetList(DataTable table, int ExtCount)
{
Hashtable HT = new Hashtable();
MaterialBomMDL model = null;
foreach (DataRow row in table.Rows)
{
string MaterialCode = row["materialcode"].ToString();
model = GetModel(row, ExtCount);
HT.Add(MaterialCode, model);
}
return(HT);
}
/// <summary>
/// 组装SQL语句
/// </summary>
/// <param name="IsExit"></param>
/// <param name="tid"></param>
/// <returns></returns>
private string AssembleSqlCode(Hashtable ParamHTList)
{
//查询合件是否存在
object obj = LocalDbDAL.ExecuteScaler(string.Format("SELECT tid FROM {0} WHERE barcode ='{1}';", BaseVariable.ResultTableName, this.lblHJCode.Text));
long TID = obj != null && obj.ToString() != "0" ? long.Parse(obj.ToString()) : 0;
string sql = "";
if (TID != 0)
{
sql += string.Format("update {0} set ", BaseVariable.ResultTableName);
#region 对应更新字段
if (ParamHTList != null && ParamHTList.Count > 0)
{
foreach (DictionaryEntry Entry in ParamHTList)
{
MaterialBomMDL model = Entry.Value as MaterialBomMDL;
if (!string.IsNullOrEmpty(model.BatchBarCode))
{
sql += string.Format("{0}='{1}',", model.FieldName, model.BatchBarCode);
}
}
}
#endregion
sql += string.Format("userid='{0}',stationid='{1}',scantype='{2}'", BaseVariable.UserEntity.UserID, BaseVariable.DeviceEntity.StationID, CurrnetScanType);
sql += string.Format(" where tid={0}", TID);
}
else
{
string field = "";
string values = "";
#region 对应更新字段
if (ParamHTList != null && ParamHTList.Count > 0)
{
foreach (DictionaryEntry Entry in ParamHTList)
{
MaterialBomMDL model = Entry.Value as MaterialBomMDL;
if (!string.IsNullOrEmpty(model.BatchBarCode))
{
field += model.FieldName + ",";
values += string.Format("'{0}',", model.BatchBarCode);
}
}
}
#endregion
field += "barcode,productcode,userid,stationid,scantype";
values += string.Format("'{0}','{1}','{2}','{3}','{4}'", this.lblHJCode.Text, this.productModel.ProductCode, BaseVariable.UserEntity.UserID, BaseVariable.DeviceEntity.StationID, CurrnetScanType);
sql += string.Format("insert into {0}({1}) values({2});", BaseVariable.ResultTableName, field, values);
}
return(sql);
}
/// <summary>
/// 返修更新信息
/// </summary>
private void Update()
{
string sql = "";
try
{
string param = context.Request.Params["param"].ToString();
//获取到的json数据
Dictionary <string, object> dict = JsonHelper.JsonDeSerializer <Dictionary <string, object> >(param);
//解析获得result的Hashtable集合
Dictionary <string, object> HTReceive = JsonHelper.JsonDeSerializer <Dictionary <string, object> >(dict["data"].ToString());
string TableName = dict["TableName"].ToString(); //表名
string HJBarCode = dict["HJBarCode"].ToString(); //合件条码
string UserID = dict["UserID"].ToString(); //用户ID
string StationID = dict["StationID"].ToString(); //工位号
string ProductCode = dict["ProductCode"].ToString(); //产品编码
string ProductType = dict["ProductType"].ToString(); //产品类型
string TraceType = "'扫描追溯','批次追溯'";
sql = string.Format("SELECT * FROM (SELECT t.* FROM (SELECT b.*, m.tablename, m.fieldname FROM productinfo p, productbominfo b, materialfield m WHERE p.productcode = b.productcode AND p.producttype = b.producttype AND b.materialcode = m.materialcode AND m.tablename='{0}') t LEFT JOIN batchno n ON t.materialcode = n.materialcode) t WHERE productcode='{1}' AND producttype='{2}' AND tracetype in({3});", TableName, ProductCode, ProductType, TraceType);
DataTable table = CommonDAL.GetDataTable(sql);
Dictionary <string, MaterialBomMDL> HTSelect = MaterialBomDAL.GetList(table);
Hashtable HTResult = new Hashtable();
foreach (var item in HTReceive)
{
MaterialBomMDL model = HTSelect[item.Key] as MaterialBomMDL;
model.BatchBarCode = item.Value.ToString();
HTResult.Add(item.Key, model);
}
//获取组装的SQL语句
sql = AssembleSqlCode(TableName, ProductType, HJBarCode, ProductCode, UserID, StationID, null, HTResult);
//CLog.WriteErrLog(sql);
if (!string.IsNullOrEmpty(sql))
{
//执行组装的SQL语句，返回结果
bool flag = CommonDAL.ExecuteSql(sql, null);
if (flag)
{
ReturnData.Code = "1";
ReturnData.Msg = "OK";
}
}
}
catch (Exception ex)
{
CLog.WriteStationLog("SQLErr", sql);
CLog.WriteErrLog(ex.Message);
}
}
/// <summary>
/// 组装SQL语句
/// </summary>
/// <param name="IsExit"></param>
/// <param name="tid"></param>
/// <returns></returns>
private string AssembleSqlCode(string TableName, string ProductType, string HJBarCode, string ProductCode, string UserID, string StationID, string CreateTime, Hashtable MaterialList)
{
//查询合件是否存在
object obj = CommonDAL.ExecuteScaler(string.Format("SELECT tid FROM {0} WHERE barcode ='{1}';", TableName, HJBarCode));
long TID = obj != null && obj.ToString() != "0" ? long.Parse(obj.ToString()) : 0;
string sql = "";
if (TID != 0)
{
sql += string.Format("update {0} set ", TableName);
#region 对应更新字段
if (MaterialList != null && MaterialList.Count > 0)
{
foreach (DictionaryEntry Entry in MaterialList)
{
MaterialBomMDL model = Entry.Value as MaterialBomMDL;
if (!string.IsNullOrEmpty(model.BatchBarCode))
{
sql += string.Format("{0}='{1}',", model.FieldName, model.BatchBarCode);
}
}
}
#endregion
int num = 0;
if (ProductType == "摇篮")
{
num = 12;
}
else if (ProductType == "踏板")
{
num = 3;
}
else if (ProductType == "制动泵")
{
num = 2;
}
else if (ProductType == "散热器")
{
num = 6;
}
if (num > 0)
{
for (int i = 1; i < num + 1; i++)
{
sql += string.Format("torque{0}=null,angle{0}=null,", i);
}
}
sql += string.Format("{0}=null,", "completed");
sql += string.Format("{0}=null,", "completetime");
sql += string.Format("{0}={1},", "createtime", "CURRENT_TIMESTAMP");
sql += string.Format("userid='{0}',stationid='{1}',", UserID, StationID);
sql += string.Format("repairstate={0}", 1);
sql += string.Format(" where tid={0}", TID);
}
else
{
sql = "";
ReturnData.Code = "201";
ReturnData.Msg = "合件不存在";
#region 用的
//string field = "";
//string values = "";
//#region 对应更新字段
//if (MaterialList != null && MaterialList.Count > 0)
//{
// foreach (DictionaryEntry Entry in MaterialList)
// {
// MaterialBomMDL model = Entry.Value as MaterialBomMDL;
// if (!string.IsNullOrEmpty(model.Ext1))
// {
// field += model.FieldName + ",";
// values += string.Format("'{0}',", model.Ext1);
// }
// }
//}
//#endregion
//if (!string.IsNullOrEmpty(CreateTime))
//{
// field += "CreateTime,";
// values += string.Format("'{0}',", CreateTime);
//}
//field += "barcode,productcode,userid,stationid";
//values += string.Format("'{0}','{1}','{2}','{3}'", HJBarCode, ProductCode, UserID, StationID);
//sql += string.Format("insert into {0}({1}) values({2});", TableName, field, values);
#endregion
}
return(sql);
}
/// <summary>
/// 得到一个对象实体（带扩展的）
/// </summary>
/// <param name="row"></param>
/// <param name="ExtCount">最大5</param>
/// <returns></returns>
public static MaterialBomMDL GetModel(DataRow row, int ExtCount)
{
MaterialBomMDL model = new MaterialBomMDL();
if (row != null)
{
if (row["tid"] != null && row["tid"].ToString() != "")
{
model.TID = long.Parse(row["tid"].ToString());
}
if (row["productcode"] != null)
{
model.ProductCode = row["productcode"].ToString();
}
if (row["productname"] != null)
{
model.ProductName = row["productname"].ToString();
}
if (row["producttype"] != null)
{
model.ProductType = row["producttype"].ToString();
}
if (row["materialcode"] != null)
{
model.MaterialCode = row["materialcode"].ToString();
}
if (row["materialname"] != null)
{
model.MaterialName = row["materialname"].ToString();
}
if (row["materialnum"] != null && row["materialnum"].ToString() != "")
{
model.MaterialNum = int.Parse(row["materialnum"].ToString());
}
if (row["batchnum"] != null && row["batchnum"].ToString() != "")
{
model.BatchNum = int.Parse(row["batchnum"].ToString());
}
if (row["fieldname"] != null)
{
model.FieldName = row["fieldname"].ToString();
}
if (row["tablename"] != null)
{
model.TableName = row["tablename"].ToString();
}
if (row["featureindex"] != null)
{
model.FeatureIndex = row["featureindex"].ToString();
}
if (row["featurecode"] != null)
{
model.FeatureCode = row["featurecode"].ToString();
}
if (row["tracetype"] != null)
{
model.TraceType = row["tracetype"].ToString();
}
//if (row["scannerid"] != null)
//{
// model.StationID = row["scannerid"].ToString();
//}
if (ExtCount > 0 && row["ext1"] != null)
{
model.Ext1 = row["ext1"].ToString();
}
if (ExtCount > 1 && row["ext2"] != null)
{
model.Ext2 = row["ext2"].ToString();
}
if (ExtCount > 2 && row["ext3"] != null)
{
model.Ext3 = row["ext3"].ToString();
}
if (ExtCount > 3 && row["ext4"] != null)
{
model.Ext4 = row["ext4"].ToString();
}
if (ExtCount > 4 && row["ext5"] != null)
{
model.Ext5 = row["ext5"].ToString();
}
}
return(model);
}
/// <summary>
/// 得到一个对象实体
/// </summary>
/// <param name="row"></param>
/// <returns></returns>
public static MaterialBomMDL GetModel(DataRow row)
{
MaterialBomMDL model = new MaterialBomMDL();
if (row != null)
{
if (row["tid"] != null && row["tid"].ToString() != "")
{
model.TID = long.Parse(row["tid"].ToString());
}
if (row["productcode"] != null)
{
model.ProductCode = row["productcode"].ToString();
}
if (row["productname"] != null)
{
model.ProductName = row["productname"].ToString();
}
if (row["producttype"] != null)
{
model.ProductType = row["producttype"].ToString();
}
if (row["materialcode"] != null)
{
model.MaterialCode = row["materialcode"].ToString();
}
if (row["materialname"] != null)
{
model.MaterialName = row["materialname"].ToString();
}
if (row["materialnum"] != null && row["materialnum"].ToString() != "")
{
model.MaterialNum = int.Parse(row["materialnum"].ToString());
}
if (row["batchnum"] != null && row["batchnum"].ToString() != "")
{
model.BatchNum = int.Parse(row["batchnum"].ToString());
}
if (row["fieldname"] != null)
{
model.FieldName = row["fieldname"].ToString();
}
if (row["tablename"] != null)
{
model.TableName = row["tablename"].ToString();
}
if (row["featureindex"] != null)
{
model.FeatureIndex = row["featureindex"].ToString();
}
if (row["featurecode"] != null)
{
model.FeatureCode = row["featurecode"].ToString();
}
if (row["tracetype"] != null)
{
model.TraceType = row["tracetype"].ToString();
}
if (row["scannerid"] != null)
{
model.StationID = row["scannerid"].ToString();
}
}
return(model);
}
