/// <summary>
/// 上传零件记录
/// </summary>
public static void UploadMaterialTable()
{
try
{
string sql = string.Format("SELECT * FROM {0}", BaseVariable.ResultTableName);
//获取当前产品的数据表
DataTable table = LocalDbDAL.GetDataTable(sql);
if (table == null || table.Rows.Count == 0)
{
return;
}
//向服务器发送请求，数据上传并返回上传成功的数据TID
string IDs = ResultDAL.Upload(BaseVariable.DeviceEntity.ProductType, BaseVariable.ResultTableName, table);
if (!string.IsNullOrEmpty(IDs))
{
sql = string.Format("DELETE FROM {0} WHERE tid IN ({1});", BaseVariable.ResultTableName, IDs);
LocalDbDAL.ExecuteSql(sql);//上传后删除本地数据
CLog.WriteStationLog("Sys", "UploadMaterialTable:TID->{" + IDs + "},Time:{" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "}");
//string[] str = IDs.Split(',');
}
}
catch (Exception ex)
{
CLog.WriteErrLog(ex.Message);
}
}
/// <summary>
/// 加载数据
/// </summary>
public void LoadData()
{
try
{
string sql = string.Format("SELECT DISTINCT(materialcode),materialname,CASE WHEN batchnum > 0 then batchnum ELSE 0 END batchnum FROM ProductBomInfo WHERE TraceType='{0}' AND ProductType = '{1}' ORDER BY materialcode ASC", "批次追溯", BaseVariable.DeviceEntity.ProductType);
DataTable table = LocalDbDAL.GetDataTable(sql);
if (table == null || table.Rows.Count < 1)
{
MaterialInfoList = null;
return;
}
this.cmbMaterialCode.Items.Add("--- 请选择 ---");
foreach (DataRow row in table.Rows)
{
BatchNoMDL model = new BatchNoMDL()
{
MaterialCode = row["MaterialCode"].ToString(),
MaterialName = row["MaterialName"].ToString(),
BatchNum = int.Parse(row["BatchNum"].ToString())
};
if (!MaterialInfoList.ContainsKey(model.MaterialCode))
{
this.cmbMaterialCode.Items.Add(model.MaterialCode);
MaterialInfoList.Add(model.MaterialCode, model);
}
}
}
catch (Exception ex)
{
CLog.WriteErrLog("[Frmbatch.LoadInfo]" + ex.Message);
}
}
/// <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>
private void UploadTable()
{
this.Invoke((EventHandler) delegate
{
try
{
Cursor.Current = Cursors.WaitCursor;
progressBar.Value = 0;
progressBar.Maximum = 1;
string sql = string.Format("SELECT * FROM {0}", BaseVariable.ResultTableName);
//获取当前产品的数据表
DataTable table = LocalDbDAL.GetDataTable(sql);
if (table == null || table.Rows.Count < 1)
{
return;
}
//向服务器发送请求，数据上传并返回上传成功的数据TID
string IDs = ResultDAL.Upload(BaseVariable.DeviceEntity.ProductType, BaseVariable.ResultTableName, table);
if (!string.IsNullOrEmpty(IDs))
{
sql = string.Format("DELETE FROM {0} WHERE tid IN ({1});", BaseVariable.ResultTableName, IDs);
LocalDbDAL.ExecuteSql(sql);//上传后删除本地数据
string [] str = IDs.Split(',');
uploadnum += str.Length;
}
ProgressTip(1);//更新进度
Cursor.Current = Cursors.Default;
}
catch (Exception ex)
{
CLog.WriteErrLog(ex.Message);
}
});
}
/// <summary>
/// 封装更新字段
/// </summary>
/// <param name="IsRemote"></param>
/// <returns></returns>
private string DataExistToDb(bool IsRemote)
{
string sql = string.Format("SELECT * FROM materialfield WHERE materialcode in(SELECT materialcode FROM productbominfo WHERE producttype='{0}' AND productcode='{1}')", BaseVariable.DeviceEntity.ProductType, resultModel.productcode);
DataTable fieldTable = null;
if (IsRemote)
{
fieldTable = CommonDAL.GetDataTable(sql); //远程获取
}
else
{
fieldTable = LocalDbDAL.GetDataTable(sql); //本地获取
}
sql = "update pedalresult set";
#region 对应更新字段
if (fieldTable != null && fieldTable.Rows.Count > 0)
{
foreach (DataRow fieldRow in fieldTable.Rows)
{
string filed = fieldRow["FieldName"].ToString();
#region 对应更新字段
switch (filed)
{
//踏板总成
case "pedalassycode":
sql += string.Format("pedalassycode='{0}',", resultModel.pedalassycode);
break;
//油门踏板
case "accelpedalcode":
sql += string.Format("accelpedalcode='{0}',", resultModel.accelpedalcode);
break;
//离合器把手批次号
case "cluthhandlebatchno":
sql += string.Format("cluthhandlebatchno='{0}',", resultModel.cluthhandlebatchno);
break;
//螺栓批次号
case "boltbatchno":
sql += string.Format("boltbatchno='{0}',", resultModel.boltbatchno);
break;
//螺母批次号
case "nutbatchno":
sql += string.Format("nutbatchno='{0}',", resultModel.nutbatchno);
break;
}
#endregion
}
}
#endregion
sql = sql.Substring(0, sql.LastIndexOf(','));
sql += string.Format(" where tid={0}", resultModel.tid);
return(sql);
}
/// <summary>
/// 加载 ListView 数据
/// </summary>
public void InitListView()
{
try
{
this.lvMaterial.Items.Clear();
this.lblProductCode.Text = this.productModel.ProductCode;
this.lblTitle.Text = "返修-" + this.productModel.ProductName;
this.scantype = ScanType.MATERIALCODE;
//获取BOM表的信息
/**
* 一次获取当前产品所有BOM信息
* 2014.11.30 By xudy
*/
string sql = string.Format("SELECT p.*,m.fieldname FROM `productbominfo` p INNER JOIN materialfield m ON p.materialcode=m.materialcode WHERE producttype='{0}' AND productcode='{1}' AND tracetype IN({2})", BaseVariable.DeviceEntity.ProductType, this.productModel.ProductCode, "'扫描追溯','批次追溯'");
DataTable table = LocalDbDAL.GetDataTable(sql);
if (table != null && table.Rows.Count > 0)
{
int index = 0;
foreach (DataRow row in table.Rows)
{
ListViewItem item = new ListViewItem(row["materialcode"].ToString()); //材料条码:0
item.SubItems.Add(row["materialname"].ToString()); //:1
item.SubItems.Add(""); //:2
item.SubItems.Add(row["featurecode"].ToString()); //特征码:3
item.SubItems.Add(row["featureindex"].ToString()); //特征位:4
item.SubItems.Add(row["fieldname"].ToString()); //字段民称:5
item.SubItems.Add(row["tracetype"].ToString()); //追溯类型:6
if (index % 2 == 0)
{
item.BackColor = Color.Gainsboro;//偶数背景色淡灰色
}
else
{
item.BackColor = Color.Silver; //奇数背景色为银色
}
item.ForeColor = Color.Black; //字体颜色为黑色
this.lvMaterial.Items.Add(item);
index++;
}
}
else
{
this.lblTip.Text = "当前型号没有追溯零件";
}
}
catch (Exception ex)
{
CLog.WriteErrLog(ex.Message);
}
}
/// <summary>
/// 添加批量追溯信息
/// </summary>
private void GetBatchInfoToParam()
{
#region 添加批量追溯信息
if (BaseVariable.DeviceEntity.ProductType != "前桥" || BaseVariable.DeviceEntity.ProductType != "后桥")
{
string sql = string.Format("SELECT tid,producttype,productcode,materialcode,materialname,materialnum,tracetype,fieldname,batchno FROM (SELECT t.*,n.batchno batchno 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)
{
foreach (DataRow row in table.Rows)
{
if (row["batchno"] != null && row["batchno"].ToString() != "")
{
RequestParam.Add(row["materialcode"].ToString(), row["batchno"].ToString());
}
}
}
}
#endregion
}
/// <summary>
/// 获取扫描追溯BOM表的信息
/// </summary>
private void InitListView()
{
this.lvMaterial.Items.Clear();
string sql = string.Format("SELECT p.*,m.fieldname FROM `productbominfo` p INNER JOIN materialfield m ON p.materialcode=m.materialcode WHERE producttype='{0}' AND productcode='{1}' AND tracetype IN({2})", BaseVariable.DeviceEntity.ProductType, this.productModel.ProductCode, "'扫描追溯'");
DataTable table = LocalDbDAL.GetDataTable(sql);
if (table != null && table.Rows.Count > 0)
{
int index = 0;
foreach (DataRow row in table.Rows)
{
ListViewItem item = new ListViewItem(row["materialcode"].ToString());//材料条码
item.SubItems.Add("");
item.SubItems.Add(row["materialnum"].ToString());
item.SubItems.Add("0");
item.SubItems.Add(row["materialname"].ToString());
item.SubItems.Add(row["featurecode"].ToString()); //特征码
item.SubItems.Add(row["featureindex"].ToString()); //特征位
item.SubItems.Add(row["fieldname"].ToString()); //字段民称
if (index % 2 == 0)
{
item.BackColor = Color.Gainsboro;//偶数背景色淡灰色
}
else
{
item.BackColor = Color.Silver; //奇数背景色为银色
}
item.ForeColor = Color.Black; //字体颜色为黑色
this.lvMaterial.Items.Add(item);
index++;
}
IsHjScan = true;//扫描合件标示为true
}
else
{
this.Tip("当前型号没有扫描追溯零件");
}
}
/// <summary>
/// 初始化特征码集合
/// </summary>
/// <returns></returns>
public static bool InitFeatureCode()
{
string sql = string.Format("SELECT DISTINCT(b.materialcode),b.materialname,p.productcode,p.producttype,p.featurecode,p.featureindex FROM productbominfo b LEFT JOIN productinfo p ON b.productcode=p.productcode WHERE p.producttype='{0}'", BaseVariable.DeviceEntity.ProductType);
DataTable table = LocalDbDAL.GetDataTable(sql);
if (table != null && table.Rows.Count > 0)
{
FeatureCodeDict = new Dictionary <string, List <string> >();
FeatureIndexSet = new Hashtable();
foreach (DataRow row in table.Rows)
{
string c = row["materialcode"].ToString();
string f = row["featurecode"].ToString();
string i = row["featureindex"].ToString();
List <string> list = new List <string>();
if (FeatureCodeDict.ContainsKey(c))
{
list = FeatureCodeDict[c];
FeatureCodeDict.Remove(c);
}
// 将特征码添加特征码的列表
list.Add(f);
// 添加零件对应特征码的列表
FeatureCodeDict.Add(c, list);
// 添加特征码对应的索引
//if (!FeatureIndexSet.ContainsKey(f))
//{
// FeatureIndexSet.Add(f + "_" + c, i);
//}
}
return(true);
}
return(false);
}
/// <summary>
/// 封装更新字段
/// </summary>
/// <param name="IsRemote"></param>
/// <returns></returns>
private string DataExistToDb(bool IsRemote)
{
string sql = string.Format("SELECT * FROM materialfield WHERE materialcode in(SELECT materialcode FROM productbominfo WHERE producttype='{0}' AND productcode='{1}')", BaseVariable.DeviceEntity.ProductType, resultModel.productcode);
DataTable fieldTable = null;
if (IsRemote)
{
fieldTable = CommonDAL.GetDataTable(sql); //远程获取
}
else
{
fieldTable = LocalDbDAL.GetDataTable(sql); //本地获取
}
sql = "";
#region 对应更新字段
if (fieldTable != null && fieldTable.Rows.Count > 0)
{
foreach (DataRow fieldRow in fieldTable.Rows)
{
string filed = fieldRow["FieldName"].ToString();
#region 对应更新字段
switch (filed)
{
//制动泵编码
case "brakepumpcode":
sql += string.Format("brakepumpcode='{0}',", resultModel.brakepumpcode);
break;
//密封垫批次号
case "gasketbatchno":
sql += string.Format("gasketbatchno='{0}',", resultModel.gasketbatchno);
break;
//六角(法兰面)螺母
case "hexagonalnutbatchno":
sql += string.Format("hexagonalnutbatchno='{0}',", resultModel.hexagonalnutbatchno);
break;
//压力传感器批次号
case "pressuresensorbatchno":
sql += string.Format("pressuresensorbatchno='{0}',", resultModel.pressuresensorbatchno);
break;
//消音器(制动泵隔音垫)
case "silencerbatchno":
sql += string.Format("silencerbatchno='{0}',", resultModel.silencerbatchno);
break;
//结合管(制动连接管)
case "connectingpipe":
sql += string.Format("connectingpipe='{0}',", resultModel.connectingpipe);
break;
//助力器制动泵支架
case "boosterbrakepumpbracket":
sql += string.Format("boosterbrakepumpbracket='{0}',", resultModel.boosterbrakepumpbracket);
break;
}
#endregion
}
}
#endregion
sql = sql.Substring(0, sql.LastIndexOf(','));
sql += string.Format(" where tid={0}", resultModel.tid);
return(sql);
}