/// <summary>
///更新並簽狀態
/// </summary>
/// <param name="pDOAHandler"></param>
/// <returns></returns>
static public bool UpdateParallelApprovalStatus(Model_DOAHandler pDOAHandler)
{
bool flag = true;
if (pDOAHandler._ParallelFlag == true)
{
sql = "UPDATE TB_GDS_ParallelApproval SET ActualCount=ActualCount+1 WHERE Plant = @Plant AND DocYear=@DocYear AND DocNo = @DocNo AND RoleCode = @RoleCode ";
opc.Clear();
opc.Add(DataPara.CreateDataParameter("@Plant", SqlDbType.VarChar, pDOAHandler._sPlant));
opc.Add(DataPara.CreateDataParameter("@DocYear", SqlDbType.VarChar, pDOAHandler._sDocYear));
opc.Add(DataPara.CreateDataParameter("@DocNo", SqlDbType.VarChar, pDOAHandler._sDocNo));
opc.Add(DataPara.CreateDataParameter("@RoleCode", SqlDbType.VarChar, pDOAHandler._sRoleCode));
try
{
sdb.ExecuteNonQuery(sql, opc);
}
catch (Exception ex)
{
flag = false;
throw new Exception("# Fail: db access error " + ex.Message);
}
}
return(flag);
}
/// <summary>
/// 記錄簽核信息至QUEUE
/// </summary>
/// <param name="GDS">CONTROL TABLE DATA</param>
/// <returns>result</returns>
static public bool AddToQueue(Model_GDS GDS)
{
bool flag = false;
opc.Clear();
string sql1 = "(@WERKS, @MBLNR_A, @MJAHR_A, @APTYP, @CASEID, @APROV, @APRD, @APRT, @REMARK, @FLAG, @UPDATE_TIME) ";
sql = "INSERT INTO TB_GDS_QUEUE " + sql1.Replace("@", "") + " VALUES " + sql1 + "";
opc.Add(DataPara.CreateDataParameter("@WERKS", SqlDbType.VarChar, GDS.WERKS));
opc.Add(DataPara.CreateDataParameter("@MBLNR_A", SqlDbType.VarChar, GDS.MBLNR_A));
opc.Add(DataPara.CreateDataParameter("@MJAHR_A", SqlDbType.Float, GDS.MJAHR_A));
opc.Add(DataPara.CreateDataParameter("@APTYP", SqlDbType.VarChar, GDS.APTYP));
opc.Add(DataPara.CreateDataParameter("@CASEID", SqlDbType.Float, GDS.CASEID));
opc.Add(DataPara.CreateDataParameter("@APROV", SqlDbType.VarChar, GDS.APROV));
opc.Add(DataPara.CreateDataParameter("@APRD", SqlDbType.VarChar, GDS.APRD));
opc.Add(DataPara.CreateDataParameter("@APRT", SqlDbType.VarChar, GDS.APRT));
opc.Add(DataPara.CreateDataParameter("@REMARK", SqlDbType.VarChar, GDS.REMARK));
opc.Add(DataPara.CreateDataParameter("@FLAG", SqlDbType.VarChar, "N"));
opc.Add(DataPara.CreateDataParameter("@UPDATE_TIME", SqlDbType.DateTime, DateTime.Now));
try
{
sdb.ExecuteNonQuery(sql, opc);
flag = true;
}
catch (Exception ex)
{
WriteLog(string.Format("# Fail: record queue data error:{0};WERKS={1};MBLNR_A={2};MJAHR_A={3}", ex.Message, GDS.WERKS, GDS.MBLNR_A, GDS.MJAHR_A), "AddToQueue");
flag = false;
}
return(flag);
}
/// <summary>
/// 記錄簽核角色為並簽情形
/// </summary>
/// <param name="pDOAHandler"></param>
/// <returns></returns>
static public bool RecordParallelApprovalInfo(Model_DOAHandler pDOAHandler)
{
bool flag = true;
if (pDOAHandler._sEndFlag == "N")
{
string[] handlerList = pDOAHandler._sHandler.Split(',');
if (handlerList.Length > 1)
{
sql = "INSERT INTO TB_GDS_ParallelApproval (Plant,DocYear,DocNo,RoleCode,TotalCount,ActualCount,UPDATE_TIME) VALUES (@Plant,@DocYear,@DocNo,@RoleCode,@TotalCount,0,getdate()) ";
opc.Clear();
opc.Add(DataPara.CreateDataParameter("@Plant", SqlDbType.VarChar, pDOAHandler._sPlant));
opc.Add(DataPara.CreateDataParameter("@DocYear", SqlDbType.VarChar, pDOAHandler._sDocYear));
opc.Add(DataPara.CreateDataParameter("@DocNo", SqlDbType.VarChar, pDOAHandler._sDocNo));
opc.Add(DataPara.CreateDataParameter("@RoleCode", SqlDbType.VarChar, pDOAHandler._sRoleCode));
opc.Add(DataPara.CreateDataParameter("@TotalCount", SqlDbType.Int, handlerList.Length));
try
{
sdb.ExecuteNonQuery(sql, opc);
}
catch (Exception ex)
{
flag = false;
throw new Exception("# Fail: db access error " + ex.Message);
}
}
}
return(flag);
}
/// <summary>
/// 舊料號判定
/// </summary>
/// <param name="roleCode"></param>
/// <returns></returns>
protected DataTable GetPartList(string roleCode)
{
sql = "SELECT * FROM TB_GDS_DOA_SPECIAL with(nolock) WHERE RoleCode = @RoleCode ";
opc.Clear();
opc.Add(DataPara.CreateDataParameter("@RoleCode", SqlDbType.VarChar, roleCode));
return(sdb.GetDataTable(sql, opc));
}
protected bool CheckJump(string LOCFrom, string LOCTo, string RoleCode, string ApType)
{
bool jumpFlag = false;
string sql = "select *from TB_GDS_DOA_JUMP Where LOCFrom=@From and LOCTo=@To and RoleCode=@RoleCode and ApType=@Type";
opc.Clear();
opc.Add(DataPara.CreateDataParameter("@From", SqlDbType.VarChar, LOCFrom));
opc.Add(DataPara.CreateDataParameter("@To", SqlDbType.VarChar, LOCTo));
opc.Add(DataPara.CreateDataParameter("@RoleCode", SqlDbType.VarChar, RoleCode));
opc.Add(DataPara.CreateDataParameter("@Type", SqlDbType.VarChar, ApType));
DataTable dt = sdb.GetDataTable(sql, opc);
if (dt.Rows.Count > 0)
{
DataRow dr = dt.Rows[0];
if (dr["JumpOption"].ToString() == "Y")
{
jumpFlag = true;
}
else
{
jumpFlag = false;
}
}
return(jumpFlag);
}
/// <summary>
/// 抓取每天/每月的用電量 累計的數據
/// </summary>
/// <param name="Parameter"></param>
/// <returns>Datatable 轉 Json</returns>
public static string GetMeterKWH(SmartMeterKWH_Input Parameter)
{
string Time = Cache_Helper.CheckIsExistByCache("SmartMeter", Parameter.functiontype, Parameter.did);
if (!string.IsNullOrEmpty(Time) && Cache_Helper.CalcTimeDifference(DateTime.Now, DateTime.Parse(Time)) < 60) //一小时更新数据
{
DataTable dt = Cache_Helper.GetAPICache("SmartMeter", Parameter.functiontype, Parameter.did);
DataRow dr = dt.Rows[0];
//string Update_Time = dr["Update_Time"].ToString();//缓存中的时间
string JsonResult = dr["Value3"].ToString();
return(JsonResult);
}
else
{
StringBuilder sb = new StringBuilder();
sb.Append(@" select T3.Line, T1.did, SUM(convert(int,T1.total)) as ActValue, Convert(int,T2.VALUE1) as TargetValue
from dbo.KWH T1, TB_APPLICATION_PARAM T2, dbo.TB_Line_Param T3
where T1.type = 'R'
and T2.PARAME_ITEM = 'upper' ");
sb.Append(" AND T2.VALUE2 = @VALUE2 and T1.did = @did and T3.LineCode = @LineCode");
opc.Clear();
switch (Parameter.functiontype)
{
case "Day":
sb.Append(" and DateDiff(dd,dt,getdate())=0 and T2.VALUE5 = 'Day'");
break;
case "Month":
sb.Append(" and DateDiff(MM,dt,getdate())=0 and T2.VALUE5 = 'Month'");
break;
default:
sb.Append(" T1.did = ''");
break;
}
sb.Append(" group by did, VALUE1, PARAME_NAME, Line");
switch (Parameter.did)
{
//OSD車間
case "190160":
case "190161":
case "190174":
case "190175":
opc.Add(DataPara.CreateDataParameter("@VALUE2", SqlDbType.NVarChar, "190160"));
opc.Add(DataPara.CreateDataParameter("@did", SqlDbType.NVarChar, Parameter.did));
opc.Add(DataPara.CreateDataParameter("@LineCode", SqlDbType.NVarChar, "190160"));
break;
default:
opc.Add(DataPara.CreateDataParameter("@VALUE2", SqlDbType.NVarChar, Parameter.did));
opc.Add(DataPara.CreateDataParameter("@did", SqlDbType.NVarChar, Parameter.did));
opc.Add(DataPara.CreateDataParameter("@LineCode", SqlDbType.NVarChar, Parameter.did));
break;
}
DataTable dt = sdb.GetDataTable(sb.ToString(), opc);
Cache_Helper.InsertCacheData("SmartMeter", Parameter.functiontype, Parameter.did, JsonConvert.SerializeObject(dt), DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
return(JsonConvert.SerializeObject(dt));
}
}
/// <summary>
/// 抓取當天預警的用電量 by 車間 最新的一條預警數據
/// </summary>
/// <param name="Parameter"></param>
/// <returns>Datatable 轉 Json</returns>
public static async Task <SmartMeterKWHAlert_Output> GetMeterKWHAlert(SmartMeterKWHAlert_Input Parameter)
{
SmartMeterKWHAlert_Output rm = new SmartMeterKWHAlert_Output();
try
{
await Task.Run(() =>
{
int Limit = GetLimitReal(Parameter.did);//根據did抓取對應的上限值
StringBuilder sb = new StringBuilder();
sb.Append(@"select Top 1 T2.Line, T1.did, T1.type, convert(varchar(16), T1.dt, 121) as dt, Convert(int,T1.total) AS ActValue, Convert(int,T3.VALUE1) AS TargetValue
from KWH T1, dbo.TB_Line_Param T2, dbo.TB_APPLICATION_PARAM T3
where T3.VALUE2 = @did and T1.did = @did and T2.LineCode = @did
and T1.type = 'R'
and DateDiff(dd, T1.dt, getdate()) = 0 and total > @total
order by dt desc");
opc.Clear();
opc.Add(DataPara.CreateDataParameter("@did", SqlDbType.NVarChar, Parameter.did));
opc.Add(DataPara.CreateDataParameter("@total", SqlDbType.Int, Limit));
DataTable dt = sdb.GetDataTable(sb.ToString(), opc);
JArray jArray = JArray.Parse(JsonConvert.SerializeObject(dt));
rm.Success = true;
rm.Status = "success";
rm.Command = "GetMeterKWHAlert";
rm.Array = jArray;
});
}
catch (Exception ex)
{
rm.Success = false;
rm.Status = "Error";
rm.Command = "GetMeterKWHAlert";
}
return(rm);
}
/// <summary>
/// BY CC,DEPT獲取簽核人
/// </summary>
/// <param name="roleCode">關卡名</param>
/// <param name="value1">CC</param>
/// <param name="value2">DEPT</param>
/// <returns>Handler</returns>
protected string GetDOAHandler_NA06(string roleCode)
{
sql = "SELECT * FROM TB_GDS_DOA_DETAIL with(nolock) WHERE RoleCode = @RoleCode ";
opc.Clear();
opc.Add(DataPara.CreateDataParameter("@RoleCode", SqlDbType.VarChar, roleCode));
return(sdb.GetRowString(sql, opc, "VALUE1"));
}
protected void Page_Load(object sender, EventArgs e)
{
if (Session["UserName"] == null)
{
Response.Redirect("~/Default.aspx");
}
else
{
lblLogonId.Text = Session["UserName"].ToString();
}
if (!IsPostBack)
{
UserRole UserRole_class = new UserRole();
if (UserRole_class.checkRole(lblLogonId.Text, function_id) == false)
{
Alert("对不起，你没有权限操作!");
return;
}
string sql = "SELECT * FROM TB_DFX_Item ";
SqlDB sdb = new SqlDB(DataPara.GetDbConnectionString("SPM"));
ArrayList opc = new ArrayList();
DataTable dt = sdb.GetDataTable(sql, opc);
BindData(grdInfo, dt);
}
}
//public static async Task<SmartMeterKWH_Output> GetMeterKWH(SmartMeterKWH_Input Parameter)
//{
// SmartMeterKWH_Output rm = new SmartMeterKWH_Output();
// try
// {
// await Task.Run(() =>
// {
// StringBuilder sb = new StringBuilder();
// sb.Append(@" select T3.Line, T1.did, SUM(convert(int,T1.total)) as ActValue, Convert(int,T2.VALUE1) as TargetValue
// from dbo.KWH T1, TB_APPLICATION_PARAM T2, dbo.TB_Line_Param T3
// where T1.type = 'R'
// and T2.PARAME_ITEM = 'upper' ");
// sb.Append(" AND T2.VALUE2 = @VALUE2 and T1.did = @did and T3.LineCode = @LineCode");
// opc.Clear();
// switch (Parameter.functiontype)
// {
// case "Day":
// sb.Append(" and DateDiff(dd,dt,getdate())=0 and T2.VALUE5 = 'Day'");
// break;
// case "Month":
// sb.Append(" and DateDiff(MM,dt,getdate())=0 and T2.VALUE5 = 'Month'");
// break;
// default:
// sb.Append(" T1.did = ''");
// break;
// }
// sb.Append(" group by did, VALUE1, PARAME_NAME, Line");
// switch (Parameter.did)
// {
// //OSD車間
// case "190160":
// case "190161":
// case "190174":
// case "190175":
// opc.Add(DataPara.CreateDataParameter("@VALUE2", SqlDbType.NVarChar, "190160"));
// opc.Add(DataPara.CreateDataParameter("@did", SqlDbType.NVarChar, Parameter.did));
// opc.Add(DataPara.CreateDataParameter("@LineCode", SqlDbType.NVarChar, "190160"));
// break;
// default:
// opc.Add(DataPara.CreateDataParameter("@VALUE2", SqlDbType.NVarChar, Parameter.did));
// opc.Add(DataPara.CreateDataParameter("@did", SqlDbType.NVarChar, Parameter.did));
// opc.Add(DataPara.CreateDataParameter("@LineCode", SqlDbType.NVarChar, Parameter.did));
// break;
// }
// DataTable dt = sdb.GetDataTable(sb.ToString(), opc);
// //WriteTraceLog.Error("Got Success");
// JArray jArray = JArray.Parse(JsonConvert.SerializeObject(dt));
// rm.Success = true;
// rm.Status = "success";
// rm.Command = "GetMeterKWH";
// rm.Array = jArray;
// });
// }
// catch (Exception ex)
// {
// //WriteTraceLog.Error("Got error. " + ex.Message);
// rm.Success = false;
// rm.Status = "Error";
// rm.Command = "GetMeterKWH";
// }
// return rm;
//}
/// <summary>
/// 智能電錶抓取昨日一天的UTS數據 上午9:20左右 MES會把數據拋到DB中
/// </summary>
/// <param name="Parameter"></param>
/// <returns></returns>
public static async Task <SmartMeterUTS_Output> GetMeterUTS(SmartMeterUTS_Input Parameter)
{
SmartMeterUTS_Output rm = new SmartMeterUTS_Output();
try
{
await Task.Run(() =>
{
StringBuilder sb = new StringBuilder();
sb.Append(@"select PLANTNO,PRODUCTLINECODE, convert(varchar(10),GR_DATE,121) as GR_DATE,QTY from UTS where PRODUCTLINECODE = @PRODUCTLINECODE and PLANTNO = @PLANTNO order by GR_DATE desc");
opc.Clear();
opc.Add(DataPara.CreateDataParameter("@PRODUCTLINECODE", SqlDbType.NVarChar, Parameter.PRODUCTLINECODE));
opc.Add(DataPara.CreateDataParameter("@PLANTNO", SqlDbType.NVarChar, Parameter.PLANTNO));
DataTable dt = sdb.GetDataTable(sb.ToString(), opc);
//WriteTraceLog.Error("Got Success");
JArray jArray = JArray.Parse(JsonConvert.SerializeObject(dt));
rm.Success = true;
rm.Status = "success";
rm.Command = "GetMeterUTS";
rm.Array = jArray;
});
}
catch (Exception ex)
{
rm.Success = false;
rm.Status = "Error";
rm.Command = "GetMeterUTS";
}
return(rm);
}
/// <summary>
/// 取行動裝置簽核欄位
/// </summary>
/// <param name="dtHead"></param>
/// <param name="dtDetail"></param>
/// <returns></returns>
public override Hashtable GetMobileFormFields(DataTable dtHead, DataTable dtDetail)
{
Hashtable ht = new Hashtable();
DataRow drHead = dtHead.Rows[0];
//string sAPTYP = drHead["APTYP"].ToString();//單據類型
ht.Add("M_LOCFM", drHead["LOCFM"].ToString()); //Sloc
ht.Add("M_KOSTL", drHead["KOSTL"].ToString()); //成本中心
sql = "select * from tb_gds_reason where GRUND = @GRUND and BWART = @BWART ";
opc.Clear();
opc.Add(DataPara.CreateDataParameter("@GRUND", SqlDbType.Float, double.Parse(drHead["GRUND"].ToString().Trim())));
opc.Add(DataPara.CreateDataParameter("@BWART", SqlDbType.VarChar, drHead["BWART"].ToString().Trim()));
ht.Add("M_GRUND", sdb.GetRowString(sql, opc, "GRTXT")); //原因
ht.Add("M_DESC", drHead["DESC"].ToString()); //中文描述
ht.Add("M_TOTALSTPRS", string.Format("{0:N4}", dtDetail.Compute("sum(STPRS)", "")).TrimEnd('0').TrimEnd('.')); //總金額(￥)
StringBuilder sbMobile = new StringBuilder();
for (int i = 0; i < dtDetail.Rows.Count; i++)
{
DataRow drDetail = dtDetail.Rows[i];
sbMobile.AppendLine(string.Format("{0} {1} {2}{3} {4}%",
Convert.ToString(i + 1),
drDetail["MATNR"].ToString().Trim(), //料號
drDetail["MENGE"].ToString().Trim(), //數量
drDetail["MEINS"].ToString().Trim(), //單位
drDetail["OVER_RATE"].ToString().Trim() //累計比例(%)
));
}
ht.Add("M_DETAIL", sbMobile.ToString());
return(ht);
}
/// <summary>
/// QueryDLBuffer
/// </summary>
/// <param name="Parameter"></param>
/// <returns>Datatable 轉 Json</returns>
public static string GetHC_QueryDLBuffer(GetHCDLBuffer_Input Parameter)
{
string Time = Cache_Helper.CheckIsExistByCache("GetHC_DLBuffer", Parameter.BU, Parameter.DEPT_ID);
if (!string.IsNullOrEmpty(Time) && DateTime.Compare(DateTime.Now, DateTime.Parse(Time)) < 0) //存在缓存则抓取缓存中的数据
{
DataTable dt = Cache_Helper.GetAPICache("GetHC_DLBuffer", Parameter.BU, Parameter.DEPT_ID);
DataRow dr = dt.Rows[0];
//string Update_Time = dr["Update_Time"].ToString();//缓存中的时间
string JsonResult = dr["Value3"].ToString();
return(JsonResult);
}
else
{
opc.Clear();
string Date = DateTime.Today.AddDays(-1).ToString("yyyy-MM-dd");
opc.Add(DataPara.CreateProcParameter("@P_BU", SqlDbType.VarChar, 10, ParameterDirection.Input, Parameter.BU));
opc.Add(DataPara.CreateProcParameter("@P_DATE", SqlDbType.VarChar, 10, ParameterDirection.Input, Date));
opc.Add(DataPara.CreateProcParameter("@P_DEPTID", SqlDbType.VarChar, 10, ParameterDirection.Input, Parameter.DEPT_ID));
DataTable dt = sdb.RunProc2("P_DailyReprot_QueryDLBuffer_API", opc);
Cache_Helper.InsertCacheData("GetHC_DLBuffer", Parameter.BU, Parameter.DEPT_ID, JsonConvert.SerializeObject(dt), DateTime.Now.AddDays(1).ToString("yyyy-MM-dd") + " 16:00:00");
return(JsonConvert.SerializeObject(dt));
}
}
protected void AfterEdit(object sender, DirectEventArgs e)
{
string CaseId = e.ExtraParams["CASEID"];
string handler = e.ExtraParams["HANDLER"];
string approver_time = e.ExtraParams["APPROVE_TIME"];
string approve_result = e.ExtraParams["APPROVE_RESULT"];
string approve_remark = e.ExtraParams["APPROVE_REMARK"];
string step_name = e.ExtraParams["STEP_NAME"];
SqlDB sdb = new SqlDB(DataPara.GetDbConnectionString("NPI_REPORT"));
////數據庫DB更新操作
string sql = " UPDATE TB_NPI_Step_Handler set APPROVE_RESULT=@approve_result, "
+ " APPROVE_REMARK=@approve_remark,SIGN_FLAG='Y',APPROVE_TIME=@approve_time"
+ " WHERE CASEID=@caseid AND STEP_NAME=@step_name and HANDLER=@handler";
ArrayList opc = new ArrayList();
opc.Add(DataPara.CreateDataParameter("@approve_time", DbType.DateTime, DateTime.Now));
opc.Add(DataPara.CreateDataParameter("@approve_result", DbType.String, approve_result));
opc.Add(DataPara.CreateDataParameter("@approve_remark", DbType.String, approve_remark));
opc.Add(DataPara.CreateDataParameter("@handler", DbType.String, handler));
opc.Add(DataPara.CreateDataParameter("@step_name", DbType.String, step_name));
opc.Add(DataPara.CreateDataParameter("@caseid", DbType.String, CaseId));
try
{
sdb.TransactionExecute(sql, opc);
}
catch (Exception ex)
{
Alert(ex.Message);
}
}
private void BindBasic(string DOCNO)
{
StringBuilder sb = new StringBuilder();
sb.Append(@"SELECT * from TB_NPI_APP_MAIN where DOC_NO = @DOC_NO ");
opc.Clear();
opc.Add(DataPara.CreateDataParameter("@DOC_NO", SqlDbType.VarChar, DOCNO));
DataTable dt = sdb.GetDataTable(sb.ToString(), opc);
if (dt.Rows.Count > 0)
{
DataRow drB = dt.Rows[0];
sbProd_group.Text = drB["PROD_GROUP"].ToString();
txtWMODEL_NAME.Text = drB["MODEL_NAME"].ToString();
txtWCUSTOMER.Text = drB["CUSTOMER"].ToString();
txtWNPIPM.Text = drB["NPI_PM"].ToString();
txtWSales.Text = drB["SALES_OWNER"].ToString();
txtWME.Text = drB["ME_ENGINEER"].ToString();
txtWEE.Text = drB["EE_ENGINEER"].ToString();
txtWCAD.Text = drB["CAD_ENGINEER"].ToString();
txtWTPPM.Text = drB["TP_PM"].ToString();
txtEVTDate.Text = drB["PROJECT_CODE"].ToString();
txtEVTRemark.Text = drB["PROJECT_CODE_REMARK"].ToString();
txtDVTDate.Text = drB["TIME_QUANTITY"].ToString();
txtDVTRemark.Text = drB["TIME_QUANTITY_REMARK"].ToString();
txtPRDate.Text = drB["PRPhaseTime"].ToString();
txtPRRemark.Text = drB["PRPhaseTime_Remark"].ToString();
}
}
/// <summary>
/// 檢查是否上傳文件
/// </summary>
/// <param name="spm_no">SPM 單號</param>
/// <returns>返回檢查信息</returns>
static private string CheckAttachment(int caseId)
{
string errMsg = string.Empty;
//查看是否有上傳附件
string sql = "SELECT FILENAME FROM ATTACHFILE WITH(NOLOCK) WHERE CASEID = @CASEID ";
ArrayList opc = new ArrayList();
opc.Add(DataPara.CreateDataParameter("@CASEID", SqlDbType.Int, caseId));
SqlDB sdb = new SqlDB(DataPara.GetDbConnectionString("SPM"));
try
{
DataTable dt = sdb.GetDataTable(sql, opc);
if (dt.Rows.Count <= 0)
{
errMsg = "Pls check your attachment</BR>";
}
}
catch (Exception)
{
throw new Exception("Check attactment status fail");
}
return(errMsg);
}
private void BindNPIReport(string Model, string PM)
{
string Building = cmbBuilding.SelectedItem.Text;
StringBuilder sb = new StringBuilder();
sb.Append(@"SELECT T1.*,T2.BU,T2.BUILDING,T2.MODEL_NAME,T2.NPI_PM,
CONVERT(varchar(10),T1.CREATE_DATE,120) AS Date,
SPM.dbo.GetHandler(T1.CASEID) AS handler
FROM TB_NPI_APP_SUB T1
LEFT JOIN TB_NPI_APP_MAIN T2
ON T1.DOC_NO=T2.DOC_NO
where Building = @Building");
opc.Clear();
opc.Add(DataPara.CreateDataParameter("@Building", SqlDbType.VarChar, Building));
if (Model.Length > 0)
{
sb.Append(" and T2.MODEL_NAME like '%'+@MODEL_NAME+'%'");
opc.Add(DataPara.CreateDataParameter("@MODEL_NAME", SqlDbType.VarChar, Model.Trim()));
}
if (PM.Length > 0)
{
sb.Append(" and T2.NPI_PM like '%'+@PM+'%'");
opc.Add(DataPara.CreateDataParameter("@PM", SqlDbType.VarChar, PM.Trim()));
}
sb.Append(" order by DOC_NO");
DataTable dt = sdb.GetDataTable(sb.ToString(), opc);
grdInfo.Store.Primary.DataSource = dt;
grdInfo.Store.Primary.DataBind();
}
/// <summary>
/// 標準DOA-依PUR GROUP 獲取簽核窗口(TYPE D)
/// </summary>
/// <param name="buyerCode"></param>
/// <returns></returns>
protected string GetDOAHandlerByBuyerCode(string buyerCode)
{
sql = "SELECT * FROM TB_GDS_BuyerCode with(nolock) WHERE PUR_GROUPID = @BuyerCode";
opc.Clear();
opc.Add(DataPara.CreateDataParameter("@BuyerCode", SqlDbType.VarChar, buyerCode));
return(sdb.GetRowString(sql, opc, "USER_ID"));
}
private void InsertVersionLog(string DFXTypeB, string DFXTypeE, string Reason)
{
SqlDB sdb = new SqlDB(DataPara.GetDbConnectionString("NPI_REPORT"));
StringBuilder sb = new StringBuilder();
sb.Append(@"
INSERT INTO [NPI_REPORT].[dbo].[TB_DFX_VersionLog]
(Building
,[DFXTypeB]
,[DFXTypeE]
,[Reason]
,[UpdateTime]
,[UpdateUser])
VALUES
( @Building
,@DFXTypeB
,@DFXTypeE
,@Reason
,@UpdateTime
,@UpdateUser)
");
opc.Clear();
opc.Add(DataPara.CreateDataParameter("@Building", DbType.String, lblBuilding.Text));
opc.Add(DataPara.CreateDataParameter("@DFXTypeB", DbType.String, DFXTypeB));
opc.Add(DataPara.CreateDataParameter("@DFXTypeE", DbType.String, DFXTypeE));
opc.Add(DataPara.CreateDataParameter("@Reason", DbType.String, Reason));
opc.Add(DataPara.CreateDataParameter("@UpdateTime", DbType.DateTime, DateTime.Now));
opc.Add(DataPara.CreateDataParameter("@UpdateUser", DbType.String, lblLogonId.Text));
sdb.TransactionExecuteScalar(sb.ToString(), opc);
}
protected void BindgrdInfoT(object sender, DirectEventArgs e)
{
string Type = cmbDFXType.SelectedItem.Text;
string ProductType = cmbProduct.SelectedItem.Text;
string Dept = cobDept.SelectedItem.Text;
StringBuilder sb = new StringBuilder();
sb.Append(@"select * from TB_DFX_Item where SUBSTRING(Item,11,1) = @Item");
opc.Clear();
opc.Add(DataPara.CreateDataParameter("@Item", DbType.String, Type));
if (cobDept.Text.Length > 0)
{
sb.Append(" and WriteDept = @WriteDept");
opc.Add(DataPara.CreateDataParameter("@WriteDept", DbType.String, Dept));
}
if (cmbProduct.Text.Length > 0)
{
sb.Append(" and ProductType = @ProductType");
opc.Add(DataPara.CreateDataParameter("@ProductType", DbType.String, ProductType));
}
DataTable dt = sdb.TransactionExecute(sb.ToString(), opc);
grdInfo.Store.Primary.DataSource = dt;
grdInfo.Store.Primary.DataBind();
}
protected string GetDOACC(string purgroupid)
{
sql = "SELECT * FROM TB_GDS_BuyerCode with(nolock) WHERE PUR_GROUPID = @PUR_GROUPID ";
opc.Clear();
opc.Add(DataPara.CreateDataParameter("@PUR_GROUPID", SqlDbType.VarChar, purgroupid));
return(sdb.GetRowString(sql, opc, "USER_ID"));
}
/// <summary>
/// BY CC,DEPT獲取簽核人
/// </summary>
/// <param name="roleCode">關卡名</param>
/// <param name="value1">CC</param>
/// <param name="value2">DEPT</param>
/// <returns>Handler</returns>
protected string GetDOAHandler_Value4(string roleCode, string value1)
{
sql = "SELECT * FROM TB_GDS_DOA_DETAIL with(nolock) WHERE roleCode = @RoleCode AND value1 = @value1";
opc.Clear();
opc.Add(DataPara.CreateDataParameter("@RoleCode", SqlDbType.VarChar, roleCode));
opc.Add(DataPara.CreateDataParameter("@value1", SqlDbType.VarChar, value1));
return(sdb.GetRowString(sql, opc, "VALUE4"));
}
private string getCLCAOpenCount(string doc_No, string sub_Doc_No)
{
string clcaOpen = "select count(1) as openCount from TB_NPI_APP_CLCA where DOC_NO=@DOC_NO and SUB_DOC_NO=@SUB_DOC_NO and RESULT = 'OPEN'";
opc.Clear();
opc.Add(DataPara.CreateProcParameter("@DOC_NO", SqlDbType.VarChar, 30, ParameterDirection.Input, doc_No));
opc.Add(DataPara.CreateProcParameter("@SUB_DOC_NO", SqlDbType.VarChar, 30, ParameterDirection.Input, sub_Doc_No));
return(sdb.GetRowString(clcaOpen, opc, "openCount"));
}
private void DeleteExist()
{
SqlDB sdb = new SqlDB(DataPara.GetDbConnectionString("NPI_REPORT"));
string sql = "Delete from [TB_Prelaunch_CheckItemConfig] where Building = @Building";
opc.Clear();
opc.Add(DataPara.CreateDataParameter("@Building", DbType.String, lblBuilding.Text));
sdb.TransactionExecuteNonQuery(sql, opc);
}
private DataTable CheckFormNo(string ModelName)
{
StringBuilder sb = new StringBuilder();
sb.Append(@"select * from TB_NPI_APP_MAIN where MODEL_NAME = @MODEL_NAME");
opc.Clear();
opc.Add(DataPara.CreateDataParameter("@MODEL_NAME", DbType.String, ModelName));
return(sdb.TransactionExecute(sb.ToString(), opc));
}
private string getCTQFailCount(string doc_No, string sub_Doc_No)
{
string ctqFail = "select count(1) as failCount from TB_NPI_APP_CTQ where DOC_NO=@DOC_NO and SUB_DOC_NO=@SUB_DOC_NO and RESULT = 'Fail'";
opc.Clear();
opc.Add(DataPara.CreateProcParameter("@DOC_NO", SqlDbType.VarChar, 30, ParameterDirection.Input, doc_No));
opc.Add(DataPara.CreateProcParameter("@SUB_DOC_NO", SqlDbType.VarChar, 30, ParameterDirection.Input, sub_Doc_No));
return(sdb.GetRowString(ctqFail, opc, "failCount"));
}
private DataTable CheckUserInfo(string Logonid)
{
StringBuilder sb = new StringBuilder();
sb.Append(@"select * from SPM.dbo.[USER] WHERE LOGONID=@LOGONID");
opc.Clear();
opc.Add(DataPara.CreateDataParameter("@LOGONID", DbType.String, Logonid));
return(sdb.TransactionExecute(sb.ToString(), opc));
}
protected void btnDelete_Click(object sender, DirectEventArgs e)
{
SpmMaster _Master = (SpmMaster)Master;
SqlDB sdb = new SqlDB(DataPara.GetDbConnectionString("NPI_REPORT"));
string subdoc = string.Empty;
string filename = string.Empty;
string filetype = string.Empty;
string CaseID = _Master.IFormURLPara.CaseId.ToString();
RowSelectionModel sm = grdAttachment.SelectionModel.Primary as RowSelectionModel;
if (sm.SelectedRows.Count <= 0)
{
Alert("請勾選需刪除的信息");
return;
}
string json = e.ExtraParams["Values"];
Dictionary <string, string>[] list = JSON.Deserialize <Dictionary <string, string>[]>(json);
StringBuilder ErrMsg = new StringBuilder();
foreach (Dictionary <string, string> row in list)
{
subdoc = row["SUB_DOC_NO"].ToString();
filename = row["FILE_NAME"].ToString();
filetype = row["FILE_TYPE"].ToString();
try
{
string sql = "DELETE FROM TB_NPI_APP_ATTACHFILE WHERE SUB_DOC_NO=@subdoc"
+ " and FILE_TYPE=@filetype and FILE_NAME=@fileName";
opc.Clear();
opc.Add(DataPara.CreateDataParameter("@subdoc", DbType.String, subdoc));
opc.Add(DataPara.CreateDataParameter("@filetype", DbType.String, filetype));
opc.Add(DataPara.CreateDataParameter("@fileName", DbType.String, filename));
sdb.TransactionExecuteNonQuery(sql, opc);
string sub_docNoPath = Server.MapPath("~/Attachment/" + CaseID + "/" + filetype + "/" + filename);
bool IsSubDocNoExist = Directory.Exists(sub_docNoPath);
if (IsSubDocNoExist)
{
DeleteExistFiles(sub_docNoPath);
}
}
catch (Exception ex)
{
ErrMsg.Append(string.Format("類別:{0},文件名稱:{1} 刪除作業失敗!ErrMsg:{2}<BR/>", filetype, filename, ex.Message));
}
}
if (ErrMsg.Length > 0)
{
Alert(ErrMsg.ToString());
}
else
{
Alert("刪除作業成功!");
BindAttachmentList(CaseID);
}
}
private void DeleteExist(string DocNo)
{
StringBuilder sb = new StringBuilder();
sb.Append(@"delete from TB_NPI_APP_MEMBER WHERE DOC_NO=@DOC_NO");
opc.Clear();
opc.Add(DataPara.CreateDataParameter("@DOC_NO", DbType.String, DocNo));
sdb.TransactionExecuteScalar(sb.ToString(), opc);
}
private void DeleteItem(string item, string ProductType)
{
string sql = "DELETE TB_DFX_Item WHERE Item=@Item and ProductType=@ProductType";
opc.Clear();
opc.Add(DataPara.CreateDataParameter("@Item", DbType.String, item));
opc.Add(DataPara.CreateDataParameter("@ProductType", DbType.String, ProductType));
sdb.TransactionExecuteNonQuery(sql, opc);
}
private DataTable GetVersionLog()
{
SqlDB sdb = new SqlDB(DataPara.GetDbConnectionString("NPI_REPORT"));
string sql = "SELECT * FROM TB_DFX_VersionLog WHERE Building=@Building";
opc.Clear();
opc.Add(DataPara.CreateDataParameter("@Building", DbType.String, lblBuilding.Text));
return(sdb.TransactionExecute(sql, opc));
}
