private DataTable GetToolUseStatus(string equipment, string device)
{
return(DBCenter.GetDataTable(@"SELECT TOOLTYPE,
SUM (NEEDQTY) AS NEEDQTY,
SUM (EQPTOOLCOUNT) AS EQPTOOLCOUNT,
OPERATION
FROM ( SELECT MES_TOOL_MAST.TOOLTYPE,
0 AS NEEDQTY,
COUNT (1) AS EQPTOOLCOUNT,
USERDEFINECOL08 OPERATION
FROM MES_EQP_TOOL
INNER JOIN MES_TOOL_MAST
ON (MES_EQP_TOOL.TOOLNAME = MES_TOOL_MAST.TOOLNAME)
WHERE MES_EQP_TOOL.EQUIPMENT = #[STRING]
GROUP BY MES_TOOL_MAST.TOOLTYPE, MES_TOOL_MAST.USERDEFINECOL08
UNION ALL
SELECT TOOLTYPE,
SUM (QUANTITY) AS NEEDQTY,
0 AS EQPTOOLCOUNT,
OPERATION
FROM CST_TOOL_DEVICE_DETAIL
WHERE EQP = #[STRING] AND DEVICE = #[STRING]
GROUP BY TOOLTYPE, OPERATION)
GROUP BY TOOLTYPE, OPERATION", equipment, equipment, device));
}
/// <summary>
/// 取的runcard的資料來源
/// </summary>
/// <param name="LotDataList"></param>
/// <returns></returns>
private DataSet GetRunCardDataSource(LotInfoEx LotData)
{
string sql = @"SELECT * FROM MES_WIP_LOT_NONACTIVE WHERE INVNO = #[STRING]";
sql = string.Format(sql);
SqlAgent sa = SQLCenter.Parse(sql, LotData.InventoryNo);
var lsInvLot = InfoCenter.GetList <LotInfoEx>(sa);
if (lsInvLot.Count == 0)
{
throw new Exception(TextMessage.Error.T00060("InventoryNO:" + LotData.InventoryNo));
}
string sBoxInString = "";
lsInvLot.ForEach(p => {
sBoxInString += "'" + p.Lot + "',";
});
sBoxInString = sBoxInString.EndsWith(",") ? sBoxInString.Substring(0, sBoxInString.Length - 1) : sBoxInString;
var dtInvData = DBCenter.GetDataTable(@"
SELECT WO,INVLOT,SUM(QUANTITY) QUANTITY,DEVICE FROM MES_WIP_LOT_NONACTIVE WHERE LOT IN (
SELECT CURRENTLOT FROM MES_WIP_COMP_NONACTIVE WHERE COMPONENTID IN (
SELECT COMPONENTID FROM CST_WIP_PACK
INNER JOIN CST_WIP_PACK_DATA ON (CST_WIP_PACK.WIP_PACK_SID = CST_WIP_PACK_DATA.WIP_PACK_SID) WHERE
BOXNO IN (" + sBoxInString + "))) GROUP BY WO,INVLOT,DEVICE");
dtInvData.TableName = "INVData";
#region 定義 LOTDATA 資料表
//DataTable dtInvData = lsInvLot.CopyDataToTable("INVData");
dtInvData.Columns.Add("INVDate");
dtInvData.Columns.Add("ITEM");
dtInvData.Columns.Add("DeviceDescr");
dtInvData.Columns.Add("Remark");
dtInvData.Columns.Add("Loaction");
dtInvData.Columns.Add("Factory");
#endregion
for (int i = 0; i < dtInvData.Rows.Count; i++)
{
dtInvData.Rows[i]["INVDate"] = lsInvLot[0]["USERDEFINECOL17"].ToCimesString();
dtInvData.Rows[i]["ITEM"] = (i + 1).ToCimesString();
dtInvData.Rows[i]["Remark"] = lsInvLot[0]["USERDEFINECOL18"].ToCimesString();
dtInvData.Rows[i]["Loaction"] = lsInvLot[0]["LOCATION"].ToCimesString();
dtInvData.Rows[i]["Factory"] = lsInvLot[0]["FACTORY"].ToCimesString();
var DeviceData = DeviceVersionInfoEx.GetActiveDeviceVersion(dtInvData.Rows[i]["DEVICE"].ToString()).ChangeTo <DeviceVersionInfoEx>();
if (DeviceData != null)
{
dtInvData.Rows[i]["DeviceDescr"] = DeviceData.Description;
}
}
DataSet dsReportData = new DataSet();
dsReportData.Tables.Add(dtInvData);
return(dsReportData);
}
private void InsertToolType(int rowIndex)
{
int index = gvQuery.Rows[rowIndex].DataItemIndex;
ArrayList SaveList = new ArrayList();
string sToolType = ((TextBox)gvQuery.Rows[rowIndex].FindControl("ttbType")).Text;
RadioButton rbtEnable = (RadioButton)gvQuery.Rows[rowIndex].FindControl("rbEnable");
TextBox ttbDescr = (TextBox)gvQuery.Rows[rowIndex].FindControl("ttbDescription");
//檢查是否有相同名稱存在的資料
sql = "SELECT 1 FROM MES_TOOL_TYPE WHERE TYPE = #[STRING]";
DataView dvCheck = DBCenter.GetDataTable(sql, sToolType).DefaultView;// Query.DoQuery(sql);
if (dvCheck != null && dvCheck.Count > 0)
{
throw new Exception(TextMessage.Error.T00710(sToolType));
}
uscAttributeSetupGrid.ValidateCheck();
//新增資料庫
ToolTypeInfo toolTypeData = InfoCenter.Create <ToolTypeInfo>();
toolTypeData.Type = sToolType;
toolTypeData.Description = ttbDescr.Text;
toolTypeData["TOOLCLASS"] = "CUTTER";
toolTypeData["UPDATETIME"] = DBCenter.GetSystemTime();
toolTypeData["USERID"] = this.User.Identity.Name;
if (rbtEnable.Checked == true)
{
toolTypeData.Status = "Enable";
toolTypeData.ActiveFlag = "T";
}
else
{
toolTypeData.Status = "Disable";
toolTypeData.ActiveFlag = "F";
}
//儲存系統屬性
_SystemAttribute.SaveSystemAttribute(toolTypeData);
TransactionStamp txnStamp = new TransactionStamp(User.Identity.Name, ProgramRight, ProgramRight, this.ApplicationName);
using (CimesTransactionScope cts = CimesTransactionScope.Create())
{
toolTypeData.InsertToDB();
uscAttributeSetupGrid.ExcuteTransaction(toolTypeData.ID);
//儲存刀壽
SaveToolLife(sToolType, txnStamp.UserID, txnStamp.RecordTime);
//儲存刀具圖檔
SaveToolPicture(sToolType, txnStamp.UserID, txnStamp.RecordTime);
cts.Complete();
}
_ProgramInformationBlock.ShowMessage(TextMessage.Hint.T00695(sToolType));
ToolTypeList[index] = toolTypeData;
}
/// <summary>
/// 取的runcard的資料來源
/// </summary>
/// <param name="LotDataList"></param>
/// <returns></returns>
private DataSet GetRunCardDataSource(LotInfoEx LotData)
{
string sql = "";
string sTableName = "MES_WIP_COMP";
if (LotData.Status == "Finished")
{
sTableName = "MES_WIP_COMP_NONACTIVE";
}
#region 定義 LOTDATA 資料表
DataTable dtLotData = lotData.CopyDataToTable("LOTDATA");
dtLotData.Columns.Add("CartonNo");
dtLotData.Columns.Add("DeviceDescr1");
dtLotData.Columns.Add("DeviceDescr2");
dtLotData.Columns.Add("CustomerNo1");
dtLotData.Columns.Add("CustomerNo2");
dtLotData.Columns.Add("Device1");
dtLotData.Columns.Add("Device2");
dtLotData.Columns.Add("Quantity1");
dtLotData.Columns.Add("Quantity2");
dtLotData.Columns.Add("Remark");
dtLotData.Columns.Add("Inspectors");
dtLotData.Columns.Add("Packers");
dtLotData.Columns.Add("InspectionDate");
#endregion
#region 定義 COMPDATA 資料表
DataTable dtCompData = new DataTable("COMPDATA");
dtCompData.Columns.Add("ComponentID1");
dtCompData.Columns.Add("ComponentID2");
dtCompData.Columns.Add("Quantity1");
dtCompData.Columns.Add("Quantity2");
#endregion
dtLotData.Rows[0]["CartonNo"] = LotData.Lot;
dtLotData.Rows[0]["Remark"] = "";
var packInfo = CSTWIPPackInfo.GetPackInfoByBoxNo(LotData.Lot);
dtLotData.Rows[0]["Inspectors"] = packInfo.INSPUSER;
dtLotData.Rows[0]["Packers"] = packInfo.UserID;
dtLotData.Rows[0]["InspectionDate"] = packInfo.UpdateTime.Substring(0, 10).Replace("/", "-");
sql = @"SELECT DEVICE,COUNT(*) QTY
FROM CST_WIP_PACK
INNER JOIN CST_WIP_PACK_DATA ON CST_WIP_PACK.WIP_PACK_SID = CST_WIP_PACK_DATA.WIP_PACK_SID
WHERE BOXNO = #[STRING]
GROUP BY DEVICE";
DataTable dtData = DBCenter.GetDataTable(sql, LotData.Lot);
int iIndex = 1;
for (int i = 0; i < dtData.Rows.Count; i++)
{
if (i >= 2)
{
break;
}
iIndex = i + 1;
dtLotData.Rows[0]["Quantity" + iIndex.ToCimesString()] = dtData.Rows[i]["QTY"].ToCimesString();
var DeviceData = DeviceVersionInfoEx.GetActiveDeviceVersion(dtData.Rows[i]["DEVICE"].ToCimesString()).ChangeTo <DeviceVersionInfoEx>();
if (DeviceData != null)
{
dtLotData.Rows[0]["DeviceDescr" + iIndex.ToCimesString()] = DeviceData.Description;
dtLotData.Rows[0]["CustomerNo" + iIndex.ToCimesString()] = DeviceData["CustomerNo"].ToCimesString();
dtLotData.Rows[0]["Device" + iIndex.ToCimesString()] = DeviceData.DeviceName;
}
#region 入庫批號
sql = @"SELECT DMC,COUNT(*) COMPONENTQTY
FROM CST_WIP_PACK
INNER JOIN CST_WIP_PACK_DATA ON CST_WIP_PACK.WIP_PACK_SID = CST_WIP_PACK_DATA.WIP_PACK_SID
WHERE BOXNO = #[STRING]
AND DEVICE = #[STRING]
GROUP BY DMC
ORDER BY DMC ";
var dt = DBCenter.GetDataTable(sql, LotData.Lot, DeviceData.DeviceName);
if (dt.Rows.Count > dtCompData.Rows.Count)
{
int iRowCount = dt.Rows.Count - dtCompData.Rows.Count;
for (int j = 0; j < iRowCount; j++)
{
DataRow dr = dtCompData.NewRow();
dtCompData.Rows.Add(dr);
}
}
for (int k = 0; k < dt.Rows.Count; k++)
{
dtCompData.Rows[k]["ComponentID" + iIndex.ToCimesString()] = dt.Rows[k]["DMC"].ToString();
dtCompData.Rows[k]["Quantity" + iIndex.ToCimesString()] = dt.Rows[k]["COMPONENTQTY"].ToString();
}
#endregion
}
dtCompData.AcceptChanges();
DataSet dsReportData = new DataSet();
dsReportData.Tables.Add(dtLotData);
dsReportData.Tables.Add(dtCompData);
return(dsReportData);
}
/// <summary>
/// 輸入生產編號
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void ttbLot_TextChanged(object sender, EventArgs e)
{
try
{
//確認是否有輸入資料
ttbLot.Must(lblLot);
#region 取得檢驗資料(查詢機台、序號、機加批號及鍛造批號欄位是否有符合介面輸入的資料，資料狀態必須為FAI且判定結果為NULL)
string sql = @"SELECT A.*, B.EQUIPMENT, B.PASSFLAG, B.BATCHID, B.DEVICE
FROM MES_QC_INSP_OBJ A
LEFT JOIN MES_QC_INSP B ON A.QC_INSP_SID = B.QC_INSP_SID
WHERE (ITEM1 = #[STRING] OR ITEM2 = #[STRING] OR ITEM3 = #[STRING] OR EQUIPMENT = #[STRING])
AND STATUS = 'FAI'
AND RESULT IS NULL";
var lot = CustomizeFunction.ConvertDMCCode(ttbLot.Text.Trim());
var table = DBCenter.GetDataTable(sql, lot, lot, lot, lot);
if (table.Rows.Count == 0)
{
//生產編號:{0} 查無檢驗資料!
throw new Exception(RuleMessage.Error.C10062(lot));
}
//清除檢驗清單資料
_QCDataList.Clear();
foreach (DataRow dr in table.Rows)
{
_QCDataList.Add(new QCData()
{
ID = dr["QC_INSP_OBJ_SID"].ToString(),
EquipmentName = dr["EQUIPMENT"].ToString(),
SecondEquipmentName = dr["ITEM5"].ToString(),
BatchID = dr["BATCHID"].ToString(),
ComponentLot = dr["ITEM1"].ToString(),
WorkOderLot = dr["ITEM2"].ToString(),
MaterialLot = dr["ITEM3"].ToString(),
ObjectSID = dr["OBJECTSID"].ToString(),
PassFlag = dr["PASSFLAG"].ToString(),
QCInspectionSID = dr["QC_INSP_SID"].ToString(),
DeviceName = dr["DEVICE"].ToString()
});
}
#endregion
#region 清除介面資料
ddlFAIReasonCode.Items.Clear();
ddlSN.Items.Clear();
ddlEquip.Items.Clear();
ttbMaterialLot.Text = "";
ttbDescr.Text = "";
ttbWorkOrderLot.Text = "";
btnOK.Enabled = false;
rdbOK.Checked = true;
rdbNG.Checked = false;
rdbPASS.Checked = false;
rdbOK.Enabled = true;
rdbNG.Enabled = false;
rdbPASS.Enabled = false;
#endregion
#region 設置機台資料
//取得所有檢驗資料之不重複機台名稱
foreach (var QCData in _QCDataList)
{
ListItem newItem = new ListItem(QCData.EquipmentName, QCData.EquipmentName);
if (ddlEquip.Items.Contains(newItem) == false)
{
ddlEquip.Items.Add(newItem);
}
}
if (ddlEquip.Items.Count == 0)
{
//生產編號:{0} 沒有機台可以選擇!
throw new Exception(RuleMessage.Error.C10063(lot));
}
if (ddlEquip.Items.Count != 1)
{
ddlEquip.Items.Insert(0, "");
}
else
{
ddlEquip.SelectedIndex = 0;
ddlEquip_SelectedIndexChanged(null, EventArgs.Empty);
}
#endregion
}
catch (Exception ex)
{
ClearField();
AjaxFocus(ttbLot);
HandleError(ex);
}
}
/// <summary>
/// 確認
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnOK_Click(object sender, EventArgs e)
{
try
{
TransactionStamp txnStamp = new TransactionStamp(User.Identity.Name, ProgramRight, ProgramRight, ApplicationName);
using (var cts = CimesTransactionScope.Create())
{
if (_SelectLotDatas.Count > 0)
{
string sLotInString = "";
// 組出where in字串
_SelectLotDatas.ForEach(p =>
{
sLotInString += "'" + p.Lot + "',";
});
sLotInString = sLotInString.EndsWith(",") ? sLotInString.Substring(0, sLotInString.Length - 1) : sLotInString;
var dtInvData = DBCenter.GetDataTable(@"SELECT WO,INVLOT,SUM(QUANTITY) QUANTITY,DEVICE,FACTORY FROM MES_WIP_LOT WHERE LOT IN(" + sLotInString + ") GROUP BY WO,INVLOT,DEVICE,FACTORY ORDER BY WO");
string headerSID = "";
string date = txnStamp.RecordTime.Substring(0, 10).Replace("/", "");
string time = txnStamp.RecordTime.Substring(11).Replace(":", "");
string preWo = string.Empty;
int seq = 1;
// SAI倉位對應表
var lstSAIWarehouse = WpcExClassItemInfo.GetInfoByClass("SAIWarehouse");
dtInvData.Rows.LoopDo <DataRow>((p, i) =>
{
string currentWO = p["WO"].ToString();
string device = p["DEVICE"].ToString();
string factory = p["FACTORY"].ToString();
string invLot = p["INVLOT"].ToString();
string qty = p["QUANTITY"].ToString();
if (preWo != currentWO)
{
seq = 1;
headerSID = DBCenter.GetSystemID();
DBCenter.ExecuteParse(@"INSERT INTO PPFHK (SID, WDATE, WTIME, BUDAT, AUFNR, FLAG)
VALUES (#[STRING], #[STRING], #[STRING], #[STRING], #[STRING],'N')", headerSID, date, time, date, currentWO);
}
//寫入PPFHP
//WERKS:Factory
//LGORT:入庫倉位
//CHARG:INVLOT
//MATNR:Device
var lstDetail = _SelectLotDatas.FindAll(select => select.InventoryLot == invLot);
var warehouseInfo = lstSAIWarehouse.Find(wpcItem => wpcItem.Remark01 == lstDetail[0].Process && wpcItem.Remark03 == "DEFECT");
if (warehouseInfo == null)
{
//T00555:查無資料，請至系統資料維護新增類別{0}、項目{1}!
throw new CimesException(TextMessage.Error.T00555("SAIWarehouse", lstDetail[0].Process + "Remark03:DEFECT"));
}
DBCenter.ExecuteParse(@"INSERT INTO PPFHP (SID, SEQNR, WDATE, WTIME, MATNR, WERKS, LGORT, CHARG, MENGE, MEINS, FLAG)
VALUES (#[STRING], #[DECIMAL], #[STRING], #[STRING], #[STRING], #[STRING], #[STRING], #[STRING], #[STRING], #[STRING], 'N')", headerSID, seq, date, time, device, factory, warehouseInfo.Remark02, invLot, qty, lstDetail[0].Unit);
int detailseq = 1;
lstDetail.ForEach(lot => {
var defectInfo = CSTWIPDefectJudgementInfo.GetDataByLot(lot.Lot);
var reasonGroupInfo = WIPReasonGroupInfoEx.GetReasonGroupByCategory(defectInfo["REASONCATEGORY"].ToString());
var reasonGroup = reasonGroupInfo == null ? "" : reasonGroupInfo.REASONGROUP;
//寫入PPBCH
DBCenter.ExecuteParse(@"INSERT INTO PPBCH (SID, MATNR, WERKS, CHARG, SEQNO, WDATE, WTIME, CLASS, ATINN, ATWRT, FLAG)
Values (#[STRING], #[STRING], #[STRING], #[STRING], #[DECIMAL], #[STRING], #[STRING], #[STRING], #[STRING], #[STRING], 'N')", headerSID, device, factory, invLot, detailseq, date, time, reasonGroup, defectInfo["REASONCATEGORY"].ToString(), defectInfo.Reason);
detailseq += 1;
});
seq += 1;
preWo = currentWO;
});
_SelectLotDatas.ForEach(lot =>
{
//寫入WMSMaster & Detail
var wmsMaster = CSTWMSMastInfo.GetMaterialLotDataByMaterialLot(lot.InventoryLot);
if (wmsMaster == null)
{
wmsMaster = InfoCenter.Create <CSTWMSMastInfo>();
wmsMaster.Lot = lot.InventoryLot;
wmsMaster.DeviceName = lot.DeviceName;
wmsMaster.RuleName = txnStamp.RuleName;
wmsMaster.InsertToDB(txnStamp.UserID, txnStamp.RecordTime);
}
//理論上只會有一個Comp
lot.GetLotAllComponents().ForEach(comp => {
var wmsDetail = InfoCenter.Create <CSTWMSDetailInfo>();
wmsDetail.Lot = lot.InventoryLot;
wmsDetail.ComponentID = comp.ComponentID;
wmsDetail.Quantity = comp.ComponentQuantity;
wmsDetail.InsertToDB(txnStamp.UserID, txnStamp.RecordTime);
});
//將入庫單號回寫到批號身上
WIPTransaction.ModifyLotSystemAttribute(lot, "INVNO", ttbDefectNo.Text, txnStamp);
//將勾選的批號結批
WIPTransaction.TerminateLot(lot, txnStamp);
});
#region 更新命名規則
if (_ExecuteNamingSQLList != null && _ExecuteNamingSQLList.Count > 0)
{
DBCenter.ExecuteSQL(_ExecuteNamingSQLList);
}
#endregion
cts.Complete();
}
else
{
//[00816]請至少選取一個{0}！
throw new Exception(TextMessage.Error.T00816(GetUIResource("WorkpieceLot")));
}
}
ClearField();
_ProgramInformationBlock.ShowMessage(TextMessage.Hint.T00614(""));
}
catch (Exception ex)
{
HandleError(ex);
}
}
/// <summary>
/// 依照檢驗計畫取得該物件的datatable
/// </summary>
/// <param name="QCTypeData"></param>
/// <param name="objectName"></param>
/// <returns></returns>
public static DataTable GetInspectionData(QCTypeInfo QCTypeData, string objectName)
{
string sql = "";
DataTable dtQCTarget = null;
#region 依照檢驗計畫取得該物件的datatable
switch (QCTypeData.QCTarget)
{
case "MES_WIP_LOT":
{
LotInfo lot = LotInfo.GetLotByLot(objectName);
if (lot == null)
{
throw new Exception(TextMessage.Error.T00378(objectName));
}
dtQCTarget = lot.CopyDataToTable(lot.ID);
}
break;
case "MES_WIP_COMP":
{
//取得component資訊以及所在的工作站
sql = @"SELECT L.OPERATION,C.* FROM MES_WIP_COMP C
INNER JOIN MES_WIP_LOT L ON C.CURRENTLOT = L.LOT
WHERE COMPONENTID = #[STRING]";
ComponentInfo comp = InfoCenter.GetBySQL <ComponentInfo>(sql, objectName);
if (comp == null)
{
throw new Exception(TextMessage.Error.T00154(objectName));
}
dtQCTarget = comp.CopyDataToTable(comp.ID);
}
break;
case "MES_CMS_CAR":
{
CarrierInfo carrier = CarrierInfo.GetCarrierByCarrierNo(objectName);
if (carrier == null)
{
throw new RuleCimesException(TextMessage.Error.T00725(objectName));
}
dtQCTarget = carrier.CopyDataToTable(carrier.ID);
}
break;
case "MES_TOOL_MAST":
{
ToolInfo tool = ToolInfo.GetToolByName(objectName);
if (tool == null)
{
throw new Exception(TextMessage.Error.T00592(objectName));
}
dtQCTarget = tool.CopyDataToTable(tool.ID);
}
break;
case "MES_MMS_MLOT":
{
MaterialLotInfo mlot = MaterialLotInfo.GetMaterialLotByMaterialLot(objectName);
if (mlot == null)
{
throw new Exception(TextMessage.Error.T00512(objectName));
}
dtQCTarget = mlot.CopyDataToTable(mlot.ID);
}
break;
case "MES_EQP_EQP":
{
EquipmentInfo equipment = EquipmentInfo.GetEquipmentByName(objectName);
if (equipment == null)
{
throw new Exception(TextMessage.Error.T00885(objectName));
}
dtQCTarget = equipment.CopyDataToTable(equipment.ID);
}
break;
default:
{
sql = string.Format("SELECT * FROM {0} WHERE {1} = #[STRING]", QCTypeData.QCTarget, QCTypeData.IdentityColumn);
dtQCTarget = DBCenter.GetDataTable(sql, objectName);
if (dtQCTarget == null || dtQCTarget.Rows.Count == 0)
{
throw new Exception(TextMessage.Error.T00030("InspectionTarget", objectName));
}
}
break;
}
#endregion
return(dtQCTarget);
}
