// public IList<string> GetDepartmentList(string model)
// {
// string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
// IList<string> ret = new List<string>();
// logger.DebugFormat("BEGIN: {0}()", methodName);
// try
// {
// string strSQL = @"select a.Department
// from ApprovalItem a
// inner join ApprovalStatus b on b.ApprovalItemID=a.ID
// and b.ModuleKeyValue=@Model";
// // string strSQL = @" SELECT '' as Model,'' as Department,
// // '' as [Status], guid,filename as UploadFileName,
// // '' as Comment,'' as Editor, '' as Cdt
// // FROM [UpLoadFile_GUID]";
// SqlParameter paraNameModel = new SqlParameter("@Model", SqlDbType.VarChar, 20);
// paraNameModel.Direction = ParameterDirection.Input;
// paraNameModel.Value = model;
// DataTable dt = SqlHelper.ExecuteDataFill(SqlHelper.ConnectionString_GetData, CommandType.Text, strSQL, paraNameModel);
// ret.Add(string.Empty);
// if (dt.Rows.Count != 0)
// {
// foreach (DataRow item in dt.Rows)
// {
// ret.Add(item["Department"].ToString().Trim());
// }
// }
// return ret;
// }
// catch (FisException e)
// {
// logger.Error(e.mErrmsg, e);
// throw new Exception(e.mErrmsg);
// }
// catch (Exception e)
// {
// logger.Error(e.Message, e);
// throw;
// }
// finally
// {
// logger.DebugFormat("END: {0}()", methodName);
// }
// }
public void SaveStatus(string model, string approvalID, int pakQty, string shipdate, string editor)
{
string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
logger.DebugFormat("BEGIN: {0}()", methodName);
try
{
FAIModelInfo faiModelItem = new FAIModelInfo();
faiModelItem.Model = model;
faiModelItem.PAKQty = pakQty;
faiModelItem.PAKStartDate =Convert.ToDateTime(shipdate);
faiModelItem.Editor = editor;
faiModelItem.Udt = DateTime.Now;
iModelRepository.UpdateFAIModel(faiModelItem);
// ApprovalStatusInfo approvalstatusItem = new ApprovalStatusInfo();
// approvalstatusItem.ID = Convert.ToInt32(approvalID);
// approvalstatusItem.Status = "Approval";
// approvalstatusItem.Comment = comment;
// approvalstatusItem.Editor = editor;
// approvalstatusItem.Udt = DateTime.Now;
// iModelRepository.UpdateApprovalStatus(approvalstatusItem);
// string strSQL = @"select Status,COUNT(1)as Qty
// from ApprovalStatus group by Status";
// DataTable dt = SqlHelper.ExecuteDataFill(SqlHelper.ConnectionString_GetData, CommandType.Text, strSQL);
// int waitingQty = 0;
// int approvedQty = 0;
// //int totoQty = 0;
// string faStatus = "";
// foreach (DataRow dr in dt.Rows)
// {
// if (dr["Status"].ToString() == "Waiting")
// {
// waitingQty = Convert.ToInt32(dr["Qty"].ToString());
// }
// if (dr["Status"].ToString() == "Approval")
// {
// approvedQty = Convert.ToInt32(dr["Qty"].ToString());
// }
// //totoQty += Convert.ToInt32(dr["Qty"].ToString());
// }
// if (waitingQty == 0 || approvedQty == 1)
// {
// if (waitingQty == 0)
// {
// faStatus = "Approval";
// SendMails(model);
// }
// else if (approvedQty == 1)
// {
// faStatus = "InApproval";
// }
// FAIModelInfo faiModelItem = new FAIModelInfo();
// faiModelItem.Model = model;
// faiModelItem.FAState = faStatus;
// faiModelItem.Editor = editor;
// faiModelItem.Udt = DateTime.Now;
// iModelRepository.UpdateFAIModel(faiModelItem);
// }
}
catch (FisException e)
{
logger.Error(e.mErrmsg, e);
throw new Exception(e.mErrmsg);
}
catch (Exception e)
{
logger.Error(e.Message, e);
throw;
}
finally
{
logger.DebugFormat("END: {0}()", methodName);
}
}
// private void SendMails(string model)
// {
// string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
// logger.DebugFormat("BEGIN: {0}()", methodName);
// try
// {
// string strSQL = @"select distinct c.OwnerEmail, c.CCEmail
// from ApprovalStatus a
// inner join FAIModel b on a.ModuleKeyValue = b.Model
// inner join ApprovalItem c on a.ApprovalItemID=c.ID and
// c.Module = (Case when b.ModelType='BTO'
// then 'FAIBTO'
// ELSE 'FAIBTF' END) and
// c.ActionName='ReleaseFA' and
// c.Department='OQC'
// where a.ModuleKeyValue=@Model";
// SqlParameter paraNameModel = new SqlParameter("@Model", SqlDbType.VarChar, 20);
// paraNameModel.Direction = ParameterDirection.Input;
// paraNameModel.Value = model;
// DataTable dt = SqlHelper.ExecuteDataFill(SqlHelper.ConnectionString_GetData, CommandType.Text, strSQL, paraNameModel);
// if (dt.Rows.Count == 0)
// {
// return;
// }
// string FromAddress = ConfigurationManager.AppSettings["FromAddress"];
// string[] ToAddress = dt.Rows[0]["OwnerEmail"].ToString().Trim().Split(',');
// string[] CcAddress = dt.Rows[0]["CCEmail"].ToString().Trim().Split(',');
// string MailSubject = ConfigurationManager.AppSettings["MailSubject"];
// string EmailServer = ConfigurationManager.AppSettings["MailServer"];
// string content = string.Format(@"Dear QC:
// IMG,DMI,SIE,PA 四個部門已對已下機型確認OK，請貴部儘快確認！
// MODEL:{0}",model);
// SendMail.Send(FromAddress,
// ToAddress,
// CcAddress,
// MailSubject,
// content,
// EmailServer);
// }
// catch (FisException e)
// {
// logger.Error(e.mErrmsg, e);
// throw new Exception(e.mErrmsg);
// }
// catch (Exception e)
// {
// logger.Error(e.Message, e);
// throw;
// }
// finally
// {
// logger.DebugFormat("END: {0}()", methodName);
// }
// }
public void ReleaseStatus(string model, string approvalID, string editor)
{
string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
logger.DebugFormat("BEGIN: {0}()", methodName);
try
{
ApprovalStatusInfo approvalstatusItem = new ApprovalStatusInfo();
approvalstatusItem.ID = Convert.ToInt32(approvalID);
approvalstatusItem.Status = "Approved";
approvalstatusItem.Editor = editor;
approvalstatusItem.Udt = DateTime.Now;
iModelRepository.UpdateApprovalStatus(approvalstatusItem);
FAIModelInfo faiModelItem = new FAIModelInfo();
faiModelItem.Model = model;
faiModelItem.PAKState = "Release";
faiModelItem.Editor = editor;
faiModelItem.Udt = DateTime.Now;
iModelRepository.UpdateFAIModel(faiModelItem);
}
catch (FisException e)
{
logger.Error(e.mErrmsg, e);
throw new Exception(e.mErrmsg);
}
catch (Exception e)
{
logger.Error(e.Message, e);
throw;
}
finally
{
logger.DebugFormat("END: {0}()", methodName);
}
}
private bool CheckFAIModel(string model, out FAIModelInfo modelFai, out Model m)
{
modelFai = iModelRepository.GetFAIModelByModel(model);
bool existsFAI = false;
if (null != modelFai)
{
if (!("Release" == modelFai.FAState && "Release" == modelFai.PAKState))
{
// 此Model：@addModel 執行FAI 中，不可Reopen !
throw new FisException("CQCHK00054", new string[] { model });
}
existsFAI = true;
}
m = iModelRepository.Find(model);
if (null == m)
{
// 不能找到Model:%1
throw new FisException("CHK804", new string[] { model });
}
return existsFAI;
}
public ArrayList GetFAIModelInfo(string model)
{
string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
logger.DebugFormat("BEGIN: {0}(model={1})", methodName, model);
try
{
ArrayList ret = new ArrayList();
string modelType = GetModelType(model);
CheckModelType(modelType);
FAIModelInfo modelFai = new FAIModelInfo();
Model m = new Model();
bool existsFAI = CheckFAIModel(model, out modelFai, out m);
ret.Add(m.Family.FamilyName);
ret.Add(modelType);
return ret;
}
catch (FisException e)
{
logger.Error(e.mErrmsg, e);
throw new Exception(e.mErrmsg);
}
catch (Exception e)
{
logger.Error(e.Message, e);
throw new SystemException(e.Message);
}
finally
{
logger.DebugFormat("END: {0}()", methodName);
}
}
public void Add(string model, string editor)
{
string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
logger.DebugFormat("BEGIN: {0}(model={1})", methodName, model);
try
{
string modelType = GetModelType(model);
CheckModelType(modelType);
FAIModelInfo modelFai = new FAIModelInfo();
Model m = new Model();
bool existsFAI = CheckFAIModel(model, out modelFai, out m);
if (existsFAI)
UpdateFAIModel(model, editor);
else
{
InsertFAIModel(model, editor);
}
}
catch (FisException e)
{
logger.Error(e.mErrmsg, e);
throw new Exception(e.mErrmsg);
}
catch (Exception e)
{
logger.Error(e.Message, e);
throw new SystemException(e.Message);
}
finally
{
logger.DebugFormat("END: {0}()", methodName);
}
}
private void InsertFAIModel(string model, string editor)
{
string OnlyNeedOQCApprove = CommonImpl.GetInstance().GetValueFromSysSetting("OnlyNeedOQCApprove");
string FAIFAQty = CommonImpl.GetInstance().GetValueFromSysSetting("FAIFAQty");
string FAIPAKQty = CommonImpl.GetInstance().GetValueFromSysSetting("FAIPAKQty");
string modelType = GetModelType(model);
string FAState = "";
// 新增ApprovalStatus
if ("Y" == OnlyNeedOQCApprove)
{
string strSQL = @"insert into ApprovalStatus(
ApprovalItemID, ModuleKeyValue,
Status, Editor, Cdt, Udt)
select
a.ID, @AddModel as ModuleKeyValue,
case when IsNeedApprove='Y'
then 'Waiting'
else 'Option'
end as [Status], @CurrentUser,
GETDATE() as Cdt, GETDATE() as Udt
from ApprovalItem a
where
a.Module= @ModelType and
a.Department = 'OQC'";
SqlParameter[] paramsArray = new SqlParameter[3];
paramsArray[0] = new SqlParameter("@CurrentUser", SqlDbType.VarChar);
paramsArray[0].Value = editor;
paramsArray[1] = new SqlParameter("@AddModel", SqlDbType.VarChar);
paramsArray[1].Value = model;
paramsArray[2] = new SqlParameter("@ModelType", SqlDbType.VarChar);
paramsArray[2].Value = "FAI" + modelType;
SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString_FA,
System.Data.CommandType.Text,
strSQL,
paramsArray);
FAState = "Approval";
}
else if (string.IsNullOrEmpty(OnlyNeedOQCApprove) || "N" == OnlyNeedOQCApprove)
{
string strSQL = @"insert into ApprovalStatus(
ApprovalItemID, ModuleKeyValue,
Status, Editor, Cdt, Udt)
select a.ID, @AddModel as ModuleKeyValue,
case when IsNeedApprove='Y'
then 'Waiting'
else 'Option'
end as [Status], @CurrentUser, GETDATE() as Cdt, GETDATE() as Udt
from ApprovalItem a
where
a.Module= @ModelType
";
SqlParameter[] paramsArray = new SqlParameter[3];
paramsArray[0] = new SqlParameter("@CurrentUser", SqlDbType.VarChar);
paramsArray[0].Value = editor;
paramsArray[1] = new SqlParameter("@AddModel", SqlDbType.VarChar);
paramsArray[1].Value = model;
paramsArray[2] = new SqlParameter("@ModelType", SqlDbType.VarChar);
paramsArray[2].Value = "FAI" + modelType;
SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString_FA,
System.Data.CommandType.Text,
strSQL,
paramsArray);
FAState = "Waiting";
}
DateTime now = DateTime.Now;
FAIModelInfo itemFai = new FAIModelInfo()
{
Model = model,
ModelType = modelType,
PlanInputDate = new DateTime(now.Year, now.Month, now.Day, 0, 0, 0, 0),
FAQty = int.Parse(FAIFAQty),
inFAQty = 0,
PAKQty = int.Parse(FAIPAKQty),
inPAKQty = 0,
PAKStartDate = now,
FAState = FAState,
PAKState = "Hold",
Remark = "KeyIn",
Editor = editor,
Cdt = now,
Udt = now
};
IUnitOfWork uow = new UnitOfWork();
iModelRepository.InsertFAIModelDefered(uow, itemFai);
uow.Commit();
}
/// <summary>
///
/// </summary>
/// <param name="faiModel"></param>
/// <param name="inQty"></param>
/// <returns></returns>
public bool CheckAndSetInFAQtyWithTrans(FAIModelInfo faiModel, int inQty)
{
if (faiModel != null && needCheckInQtyFAIFAState.Contains(faiModel.FAState))
{
int remainingQty = faiModel.FAQty - faiModel.inFAQty;
if ((remainingQty - inQty) < 0)
{
throw new FisException("CQCHK50005", new List<string>{faiModel.Model,
"FA Travel Card Station",
remainingQty.ToString(),
inQty.ToString()});
}
faiModel.inFAQty = faiModel.inFAQty + inQty;
if (faiModel.FAState == "Approval")
{
faiModel.FAState = "Pilot";
}
faiModel.Editor = "FAI" + faiModel.Editor;
return true;
}
return false;
}
public void RemoveStatus(string model, string approvalID, string comment, string editor)
{
string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
logger.DebugFormat("BEGIN: {0}()", methodName);
try
{
ApprovalStatusInfo approvalstatusItem = new ApprovalStatusInfo();
approvalstatusItem.ID = Convert.ToInt32(approvalID);
approvalstatusItem.Status = "Waiting";
approvalstatusItem.Comment = comment;
approvalstatusItem.Editor = editor;
approvalstatusItem.Udt = DateTime.Now;
iModelRepository.UpdateApprovalStatus(approvalstatusItem);
string strSQL = @"select a.Status,COUNT(1)as Qty
from ApprovalStatus a
inner join ApprovalItem b on a.ApprovalItemID = b.ID and b.IsNeedApprove = 'Y'
inner join FAIModel c on a.ModuleKeyValue = c.Model
where a.ModuleKeyValue =@Model
and b.Module = 'FAI'+ rtrim(c.ModelType)
and b.ActionName='ReleaseFA'
group by a.Status";
SqlParameter paraNameModel = new SqlParameter("@Model", SqlDbType.VarChar, 20);
paraNameModel.Direction = ParameterDirection.Input;
paraNameModel.Value = model;
DataTable dt = SqlHelper.ExecuteDataFill(SqlHelper.ConnectionString_GetData, CommandType.Text, strSQL, paraNameModel);
int approvedQty = 0;
string faStatus = "";
foreach (DataRow dr in dt.Rows)
{
if (dr["Status"].ToString() == "Approved")
{
approvedQty = Convert.ToInt32(dr["Qty"].ToString());
}
}
if (approvedQty == 0)
{
faStatus = "Waiting";
}
else
{
faStatus = "InApproval";
}
FAIModelInfo faiModelItem = new FAIModelInfo();
faiModelItem.Model = model;
faiModelItem.FAState = faStatus;
faiModelItem.Editor = editor;
faiModelItem.Udt = DateTime.Now;
iModelRepository.UpdateFAIModel(faiModelItem);
}
catch (FisException e)
{
logger.Error(e.mErrmsg, e);
throw new Exception(e.mErrmsg);
}
catch (Exception e)
{
logger.Error(e.Message, e);
throw;
}
finally
{
logger.DebugFormat("END: {0}()", methodName);
}
}
public void CheckApprovalStatusAndChengeStatus(string model, string approvalStatusID, string approvalItemID, string comment, string editor, string family,
string department, string isNeedUploadFile, string filename)
{
string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
logger.DebugFormat("BEGIN: {0}()", methodName);
try
{
string strSQL = "";
if (isNeedUploadFile == "Y" && string.IsNullOrEmpty(filename))
{
strSQL = @"select * from ApprovalItemAttr
where ApprovalItemID =@approvalID
and AttrName = 'FamilyNeedUploadFile'
and AttrValue in ('ALL', @family) ";
SqlParameter paraNameApprovalID = new SqlParameter("@approvalID", SqlDbType.VarChar, 20);
paraNameApprovalID.Direction = ParameterDirection.Input;
paraNameApprovalID.Value = approvalItemID;
SqlParameter paraNameFamily = new SqlParameter("@family", SqlDbType.VarChar, 20);
paraNameFamily.Direction = ParameterDirection.Input;
paraNameFamily.Value = family;
DataTable dt = SqlHelper.ExecuteDataFill(SqlHelper.ConnectionString_GetData, CommandType.Text, strSQL, paraNameApprovalID,
paraNameFamily);
if (dt.Rows.Count > 0)
{
throw new FisException("CQCHK50112", new string[] { });
}
}
ApprovalStatusInfo approvalstatusItem = new ApprovalStatusInfo();
approvalstatusItem.ID = Convert.ToInt32(approvalStatusID);
approvalstatusItem.Status = "Approved";
approvalstatusItem.Comment = comment;
approvalstatusItem.Editor = editor;
approvalstatusItem.Udt = DateTime.Now;
iModelRepository.UpdateApprovalStatus(approvalstatusItem);
if (department == "OQC")
{
strSQL = @" select a.Status,COUNT(1)as Qty
from ApprovalStatus a
inner join ApprovalItem b on a.ApprovalItemID = b.ID
inner join FAIModel c on a.ModuleKeyValue = c.Model
where b.Module = 'FAI'+ rtrim(c.ModelType) and
b.ActionName = 'ReleaseFA' and
b.IsNeedApprove = 'Y' and
a.ModuleKeyValue=@Model
group by a.Status";
SqlParameter paraNameModel = new SqlParameter("@Model", SqlDbType.VarChar, 20);
paraNameModel.Direction = ParameterDirection.Input;
paraNameModel.Value = model;
DataTable dt = SqlHelper.ExecuteDataFill(SqlHelper.ConnectionString_GetData, CommandType.Text, strSQL, paraNameModel);
int waitingQty = 0;
int approvedQty = 0;
string faStatus = "";
foreach (DataRow dr in dt.Rows)
{
if (dr["Status"].ToString() == "Waiting")
{
waitingQty = Convert.ToInt32(dr["Qty"].ToString());
}
if (dr["Status"].ToString() == "Approved")
{
approvedQty = Convert.ToInt32(dr["Qty"].ToString());
}
}
if (waitingQty > 0)
{
throw new FisException("CQCHK50113", new string[] { });
}
if (waitingQty == 0)
{
faStatus = "Release";
SendMails(model);
}
FAIModelInfo faiModelItem = new FAIModelInfo();
faiModelItem.Model = model;
faiModelItem.FAState = faStatus;
faiModelItem.Editor = editor;
faiModelItem.Udt = DateTime.Now;
iModelRepository.UpdateFAIModel(faiModelItem);
}
else
{
strSQL = @"select a.Status,COUNT(1)as Qty
from ApprovalStatus a
inner join ApprovalItem b on a.ApprovalItemID = b.ID and b.Department <> 'OQC'
inner join FAIModel c on a.ModuleKeyValue = c.Model
and b.Module = 'FAI'+ rtrim(c.ModelType)
and b.IsNeedApprove = 'Y' and b.ActionName='ReleaseFA'
where a.ModuleKeyValue =@Model
group by a.Status";
SqlParameter paraNameModel = new SqlParameter("@Model", SqlDbType.VarChar, 20);
paraNameModel.Direction = ParameterDirection.Input;
paraNameModel.Value = model;
DataTable dt = SqlHelper.ExecuteDataFill(SqlHelper.ConnectionString_GetData, CommandType.Text, strSQL, paraNameModel);
int waitingQty = 0;
int approvedQty = 0;
string faStatus = "Waiting";
foreach (DataRow dr in dt.Rows)
{
if (dr["Status"].ToString() == "Waiting")
{
waitingQty = Convert.ToInt32(dr["Qty"].ToString());
}
if (dr["Status"].ToString() == "Approved")
{
approvedQty = Convert.ToInt32(dr["Qty"].ToString());
}
}
if (waitingQty == 0)
{
faStatus = "Approval";
SendMails(model);
}
else if (approvedQty == 1)
{
faStatus = "InApproval";
}
FAIModelInfo faiModelItem = new FAIModelInfo();
faiModelItem.Model = model;
faiModelItem.FAState = faStatus;
faiModelItem.Editor = editor;
faiModelItem.Udt = DateTime.Now;
iModelRepository.UpdateFAIModel(faiModelItem);
}
}
catch (FisException e)
{
logger.Error(e.mErrmsg, e);
throw new Exception(e.mErrmsg);
}
catch (Exception e)
{
logger.Error(e.Message, e);
throw;
}
finally
{
logger.DebugFormat("END: {0}()", methodName);
}
}