/// <summary>
/// 添加不合格品处置单
/// </summary>
/// <param name="model">不合格品处置单 </param>
/// <param name="detail">明细</param>
/// <param name="htExtAttr">扩展属性</param>
/// <returns></returns>
public static bool AddNoPass(CheckNotPassModel model, List<CheckNotPassDetailModel> detail, Hashtable htExtAttr)
{
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
try
{
bool result = false;
LogInfoModel logModel = InitLogInfo(model.ProcessNo);
logModel.Element = ConstUtil.LOG_PROCESS_INSERT;
result = CheckNotPassDBHelper.AddNoPass(model, detail, htExtAttr);
if (!result)
{
logModel.Remark = ConstUtil.LOG_PROCESS_FAILED;
}
else
{
logModel.Remark = ConstUtil.LOG_PROCESS_SUCCESS;
model.ID = IDIdentityUtil.GetIDIdentity("officedba.CheckNotPass");
}
LogDBHelper.InsertLog(logModel);
return result;
}
catch (Exception ex)
{
WriteSystemLog(userInfo, ex);
return false;
}
}
protected void btnImport_Click(object sender, ImageClickEventArgs e)
{
string companyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD;
CheckNotPassModel model = new CheckNotPassModel();
model.CompanyCD = companyCD;
string ProductID = this.hiddenProductID.Value;
model.Title = this.txtSubject.Text.Trim();
model.ProcessNo = txtReportNo.Text.Trim();
model.ReportID = int.Parse(hiddenFromReportID.Value);
model.BillStatus = sltBillStatus.Value;
string theBeginTme = BeginTime.Value;
string theEndTime = EndTime.Value;
string FlowStatus = ddlFlowStatus.Value;
int TotalCount = 0;
model.Attachment = " ID Desc";
if (this.hiddenOrder.Value != "0")
{
string[] myOrder = this.hiddenOrder.Value.Split('_');
if (myOrder[1] == "a")
{
model.Attachment = myOrder[0] + " asc ";
}
else
{
model.Attachment = myOrder[0] + " desc ";
}
}
model.Creator = -100;
model.Confirmor = -100;
string EFIndex = GetBillExAttrControl1.GetExtIndexValue;
string EFDesc = GetBillExAttrControl1.GetExtTxtValue;
DataTable dt = CheckNotPassBus.SearchNoPass(model, ProductID, theBeginTme, theEndTime, FlowStatus, EFIndex, EFDesc, ref TotalCount);
//导出标题
string headerTitle = "单据编号|单据主题|源单类型|质检报告单|物品编号|物品名称|处理负责人|处理日期|单据状态|审批状态";
string[] header = headerTitle.Split('|');
//导出标题所对应的列字段名称
string columnFiled = "ProcessNo|Title|FromTypeName|ReportNo|ProNo|ProductName|EmployeeName|ProcessDate|BillStatus|FlowStatus";
string[] field = columnFiled.Split('|');
XBase.Common.OutputToExecl.ExportToTable(this.Page, dt, header, field, "不合格品处置单列表");
}
public static bool CloseBill(CheckNotPassModel model, string Method)
{
return CheckNotPassDBHelper.CloseBill(model, Method);
}
/// <summary>
/// 更新不合格品处置单
/// </summary>
/// <param name="model">不合格品处置单 </param>
/// <param name="detailmodel">明细</param>
/// <param name="SortID"></param>
/// <param name="htExtAttr">扩展属性</param>
/// <returns></returns>
public static bool UpdateNoPassInfo(CheckNotPassModel model, List<CheckNotPassDetailModel> detail, string[] SortID, Hashtable htExtAttr)
{
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
try
{
bool succ = false;
LogInfoModel logModel = InitLogInfo(model.ProcessNo);
logModel.Element = ConstUtil.LOG_PROCESS_UPDATE;
succ = CheckNotPassDBHelper.UpdateNoPassInfo(model, detail, SortID, htExtAttr);
if (!succ)
logModel.Remark = ConstUtil.LOG_PROCESS_FAILED;
else
logModel.Remark = ConstUtil.LOG_PROCESS_SUCCESS;
LogDBHelper.InsertLog(logModel);
return succ;
}
catch (Exception ex)
{
WriteSystemLog(userInfo, ex);
return false;
}
}
public static DataTable GetNoPassDetailInfo(CheckNotPassModel model)
{
return CheckNotPassDBHelper.GetNoPassDetail(model);
}
public static DataTable GetNoPassInfo(CheckNotPassModel modle)
{
return CheckNotPassDBHelper.GetNoPassInfo(modle);
}
/// <summary>
/// 检索
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static DataTable GetAllNoPass(CheckNotPassModel model, string ProductID, string BetinTime, string EndTime, string FlowStatus, string EFIndex, string EFDesc, ref int TotalCount)
{
string sql = "";
int BillTypeFlag = int.Parse(ConstUtil.CODING_RULE_StorageQuality_NO);
int BillTypeCode = int.Parse(ConstUtil.CODING_RULE_StorageNOPass_NO);
sql += "select * from (";
sql += " SELECT c.[ID],c.ModifiedDate ";
sql += " ,c.[ProcessNo] ";
sql += " ,c.[FromType] ";
sql += " ,(case c.FromType when '1'then '质检报告单' else '' end) as FromTypeName ";
sql += " ,c.[ReportID],isnull(q.ReportNo,'') as ReportNo ";
sql += " ,CASE f.FlowStatus WHEN '1' THEN '待审批' WHEN '2' THEN '审批中' WHEN '3' THEN '审批通过' WHEN '4' THEN '审批不通过' when '5' then '撤消审批' ELSE '' END AS FlowStatus";
sql += " ,CASE c.BillStatus WHEN '1' THEN '制单' WHEN '2' THEN '执行' WHEN '3' THEN '变更' WHEN '4' THEN '手工结单' WHEN '5' THEN '自动结单' ELSE '' END AS BillStatus";
sql += " ,isnull(e.[EmployeeName],'') as EmployeeName ";
sql += " ,isnull(substring(CONVERT(varchar,c.ProcessDate,120),0,11),'') as ProcessDate ";
sql += " ,isnull(c.[BillStatus],'0') as BillStatusID ";
sql += " ,isnull(c.[Title],'') as Title ";
sql += " ,isnull(p.ProductName,'') as ProductName ";
sql += " ,isnull(p.ProdNo,'') as ProNo ,isnull(f.FlowStatus,'0') as FlowStatusID";
sql += " FROM [officedba].[CheckNotPass] as c left join officedba.QualityCheckReport as q ";
sql += " on c.ReportID=q.ID left join officedba.ProductInfo as p on q.ProductID=p.ID ";
sql += " left join officedba.EmployeeInfo as e on c.Executor=e.ID ";
sql += " left join officedba.FlowInstance AS f ON c.CompanyCD = f.CompanyCD AND f.BillTypeFlag = " + BillTypeFlag + " AND f.BillTypeCode = " + BillTypeCode + " AND c.ID = f.BillID ";
sql += " and f.ID=(select max(ID) from officedba.FlowInstance where c.CompanyCD = officedba.FlowInstance.CompanyCD AND officedba.FlowInstance.BillTypeFlag = " + BillTypeFlag + " AND officedba.FlowInstance.BillTypeCode = " + BillTypeCode + " AND c.ID = officedba.FlowInstance.BillID) ";
sql += " where c.CompanyCD=@CompanyCD ";
SqlCommand comm = new SqlCommand();
comm.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
if (ProductID != "0" && ProductID.ToString().Trim() != "")
{
sql += " and p.ID=@ProductID ";
comm.Parameters.Add(SqlHelper.GetParameter("@ProductID", ProductID));
}
if (!string.IsNullOrEmpty(model.Title))
{
sql += " and c.Title like @Title ";
comm.Parameters.Add(SqlHelper.GetParameter("@Title", "%" + model.Title + "%"));
}
if (!string.IsNullOrEmpty(model.ProcessNo))
{
sql += " and c.ProcessNo like @ProcessNo ";
comm.Parameters.Add(SqlHelper.GetParameter("@ProcessNo", "%" + model.ProcessNo + "%"));
}
if (model.ReportID != 0)
{
sql += " and c.ReportID=@ReportID";
comm.Parameters.Add(SqlHelper.GetParameter("@ReportID", model.ReportID));
}
if (model.BillStatus != "00")
{
sql += " and c.BillStatus=@BillStatus";
comm.Parameters.Add(SqlHelper.GetParameter("@BillStatus", model.BillStatus));
}
if (!string.IsNullOrEmpty(BetinTime))
{
sql += " and c.ProcessDate>=@BeginTime";
comm.Parameters.Add(SqlHelper.GetParameter("@BeginTime", BetinTime));
}
if (!string.IsNullOrEmpty(EFIndex) && !string.IsNullOrEmpty(EFDesc))
{
sql += " and c.ExtField" + EFIndex + " LIKE @EFDesc ";
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EFDesc", "%" + EFDesc + "%"));
}
if (!string.IsNullOrEmpty(EndTime))
{
sql += " and c.ProcessDate<=@EndTime";
comm.Parameters.Add(SqlHelper.GetParameter("@EndTime", EndTime));
}
if (FlowStatus != "00" && FlowStatus != "6")
{
sql += " and f.FlowStatus=@FlowStatus";
comm.Parameters.Add(SqlHelper.GetParameter("@FlowStatus", FlowStatus));
}
sql += " ) as Info";
if (FlowStatus == "6")
{
sql += " where FlowStatusID=@FlowStatus1";
comm.Parameters.Add(SqlHelper.GetParameter("@FlowStatus1", "0"));
}
if (model.Creator == -100)
{
sql += " order by " + model.Attachment;
}
comm.CommandText = sql;
DataTable dt = new DataTable();
if (model.Creator == -100)
{
dt = SqlHelper.ExecuteSearch(comm);
}
else
{
dt = SqlHelper.PagerWithCommand(comm, model.Creator, model.Confirmor, model.Attachment, ref TotalCount);
}
return dt;
}
public static bool UnConfirmBill(CheckNotPassModel model, string CheckNum)
{
int BillTypeFlag = int.Parse(ConstUtil.BILL_TYPECODE_STORAGE_QUALITY);
int BillTypeCode = int.Parse(ConstUtil.BILL_TYPECODE_STORAGE_NOPASS);
ArrayList sqllist = new ArrayList();
bool returnvalue = false;
SqlCommand thecomm = new SqlCommand();
string thesql = "select BillStatus,ReportID from officedba.CheckNotPass where ID=@ID";
thecomm.Parameters.Add(SqlHelper.GetParameter("@ID", model.ID));
thecomm.CommandText = thesql;
string BillStatus = "0";
DataTable thedt = SqlHelper.ExecuteSearch(thecomm);
if (thedt.Rows.Count > 0)
{
BillStatus = thedt.Rows[0]["BillStatus"].ToString();
}
//#region 根据报告单ID获取 源单信息
//SqlCommand reportComm = new SqlCommand();
//string reportsql = "select FromType,FromDetailID from officedba.QualityCheckReport where ID=@ID";
//reportComm.Parameters.Add(SqlHelper.GetParameter("@ID", ReportID));
//reportComm.CommandText = reportsql;
//DataTable reportDt = SqlHelper.ExecuteSearch(reportComm);
//if (reportDt.Rows.Count > 0)
//{
// FromDetailID = reportDt.Rows[0]["FromDetailID"].ToString();
// FromType = reportDt.Rows[0]["FromType"].ToString();
//}
//#endregion
if (BillStatus == "2")
{
SqlCommand comm = new SqlCommand();
string sql = "Update officedba.CheckNotPass set BillStatus='1',ModifiedUserID=@ModifiedUserID,ModifiedDate=@ModifiedDate where ID=@ID";
comm.Parameters.Add(SqlHelper.GetParameter("@ID", model.ID));
comm.Parameters.Add(SqlHelper.GetParameter("@ModifiedUserID", model.ModifiedUserID));
comm.Parameters.Add(SqlHelper.GetParameter("@ModifiedDate", model.ModifiedDate));
comm.CommandText = sql;
sqllist.Add(comm);
//#region 回写需要 -回写质检报告单源单类型为采购到货的数据 --不需要
//if (FromType == "4")
//{
// SqlCommand purcomm = new SqlCommand();
// string pursql = "update officedba.PurchaseArriveDetail set RejectCount=isnull(RejectCount,0)-@RejectCount where ID=@ID";
// purcomm.Parameters.Add(SqlHelper.GetParameter("@ID", FromDetailID));
// purcomm.Parameters.Add(SqlHelper.GetParameter("@RejectCount", CheckNum));
// purcomm.CommandText = pursql;
// sqllist.Add(purcomm);
//}
//#endregion
#region 撤消审批流程
DataTable dtFlowInstance = Common.FlowDBHelper.GetFlowInstanceInfo(model.CompanyCD, BillTypeFlag, BillTypeCode, model.ID);
if (dtFlowInstance.Rows.Count > 0)
{
//提交审批了的单据
string FlowInstanceID = dtFlowInstance.Rows[0]["FlowInstanceID"].ToString();
string FlowStatus = dtFlowInstance.Rows[0]["FlowStatus"].ToString();
string FlowNo = dtFlowInstance.Rows[0]["FlowNo"].ToString();
#region 往流程任务历史记录表
StringBuilder sqlHis = new StringBuilder();
sqlHis.AppendLine("Insert into officedba.FlowTaskHistory(CompanyCD,FlowInstanceID,FlowNo,BillTypeID,BillID,StepNo,State,operateUserId,operateDate)");
sqlHis.AppendLine("Values(@CompanyCD,@tempFlowInstanceID,@tempFlowNo,@BillTypeFlag,@BillID,0,2,@ModifiedUserID,getdate())");
SqlCommand commHis = new SqlCommand();
commHis.CommandText = sqlHis.ToString();
commHis.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
commHis.Parameters.Add(SqlHelper.GetParameter("@tempFlowInstanceID", FlowInstanceID));
commHis.Parameters.Add(SqlHelper.GetParameter("@tempFlowNo", FlowNo));
commHis.Parameters.Add(SqlHelper.GetParameter("@BillTypeFlag", BillTypeFlag));
commHis.Parameters.Add(SqlHelper.GetParameter("@BillID", model.ID));
commHis.Parameters.Add(SqlHelper.GetParameter("@ModifiedUserID", model.ModifiedUserID));
sqllist.Add(commHis);
#endregion
#region 更新流程任务处理表
StringBuilder sqlTask = new StringBuilder();
sqlTask.AppendLine("Update officedba.FlowTaskList Set StepNo=0,ModifiedDate=getdate(),ModifiedUserID=@ModifiedUserID");
sqlTask.AppendLine("Where CompanyCD=@CompanyCD and FlowInstanceID=@tempFlowInstanceID");
SqlCommand commTask = new SqlCommand();
commTask.CommandText = sqlTask.ToString();
commTask.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
commTask.Parameters.Add(SqlHelper.GetParameter("@tempFlowInstanceID", FlowInstanceID));
commTask.Parameters.Add(SqlHelper.GetParameter("@ModifiedUserID", model.ModifiedUserID));
sqllist.Add(commTask);
#endregion
#region 更新流程实例表
StringBuilder sqlIns = new StringBuilder();
sqlIns.AppendLine("Update officedba.FlowInstance Set FlowStatus=5,ModifiedDate=getdate(),ModifiedUserID=@ModifiedUserID ");
sqlIns.AppendLine("Where CompanyCD=@CompanyCD ");
sqlIns.AppendLine("and FlowNo=@tempFlowNo ");
sqlIns.AppendLine("and BillTypeFlag=@BillTypeFlag ");
sqlIns.AppendLine("and BillTypeCode=@BillTypeCode ");
sqlIns.AppendLine("and BillID=@BillID");
SqlCommand commIns = new SqlCommand();
commIns.CommandText = sqlIns.ToString();
commIns.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
commIns.Parameters.Add(SqlHelper.GetParameter("@tempFlowNo", FlowNo));
commIns.Parameters.Add(SqlHelper.GetParameter("@BillTypeFlag", BillTypeFlag));
commIns.Parameters.Add(SqlHelper.GetParameter("@BillTypeCode", BillTypeCode));
commIns.Parameters.Add(SqlHelper.GetParameter("@BillID", model.ID));
commIns.Parameters.Add(SqlHelper.GetParameter("@ModifiedUserID", model.ModifiedUserID));
sqllist.Add(commIns);
#endregion
}
#endregion
}
else
{
returnvalue = false;
}
if (BillStatus == "2")
{
if (SqlHelper.ExecuteTransWithArrayList(sqllist))
{
returnvalue = true;
}
else
{
returnvalue = false;
}
}
return returnvalue;
}
public static bool ConfirmBill(CheckNotPassModel model, string CheckNum)
{
ArrayList sqllist = new ArrayList();
bool returnvalue = false;
SqlCommand thecomm = new SqlCommand();
string thesql = "select BillStatus,ReportID from officedba.CheckNotPass where ID=@ID";
thecomm.Parameters.Add(SqlHelper.GetParameter("@ID", model.ID));
thecomm.CommandText = thesql;
string BillStatus = "0";
string ReportID = "0";
string FromDetailID = "0";
string FromType = "00";
DataTable thedt = SqlHelper.ExecuteSearch(thecomm);
if (thedt.Rows.Count > 0)
{
BillStatus = thedt.Rows[0]["BillStatus"].ToString();
ReportID = thedt.Rows[0]["ReportID"].ToString();
}
#region 根据报告单ID获取 源单信息
SqlCommand reportComm = new SqlCommand();
string reportsql = "select FromType,FromDetailID from officedba.QualityCheckReport where ID=@ID";
reportComm.Parameters.Add(SqlHelper.GetParameter("@ID", ReportID));
reportComm.CommandText = reportsql;
DataTable reportDt = SqlHelper.ExecuteSearch(reportComm);
if (reportDt.Rows.Count > 0)
{
FromDetailID = reportDt.Rows[0]["FromDetailID"].ToString();
FromType = reportDt.Rows[0]["FromType"].ToString();
}
#endregion
if (BillStatus == "1")
{
StringBuilder sql = new StringBuilder();
SqlCommand comm = new SqlCommand();
sql.AppendLine(" UPDATE officedba.CheckNotPass SET");
sql.AppendLine(" Confirmor = @Confirmor,");
sql.AppendLine(" ConfirmorDate = @ConfirmorDate,");
sql.AppendLine(" BillStatus = 2,");
sql.AppendLine(" ModifiedUserID = @ModifiedUserID,");
sql.AppendLine(" ModifiedDate = @ModifiedDate ");
sql.AppendLine(" Where CompanyCD=@CompanyCD and ID=@ID");
comm.Parameters.Add(SqlHelper.GetParameter("@ID", model.ID));
comm.Parameters.Add(SqlHelper.GetParameter("@Confirmor", model.Confirmor));
comm.Parameters.Add(SqlHelper.GetParameter("@ConfirmorDate", model.ConfirmorDate));
comm.Parameters.Add(SqlHelper.GetParameter("@ModifiedUserID", model.ModifiedUserID));
comm.Parameters.Add(SqlHelper.GetParameter("@ModifiedDate", model.ModifiedDate));
comm.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD.Trim()));
comm.CommandText = sql.ToString();
sqllist.Add(comm);
//#region 回写需要 -回写质检报告单源单类型为采购到货的数据 ---不需要
//if (FromType == "4")
//{
// SqlCommand purcomm = new SqlCommand();
// string pursql = "update officedba.PurchaseArriveDetail set RejectCount=isnull(RejectCount,0)+@RejectCount where ID=@ID";
// purcomm.Parameters.Add(SqlHelper.GetParameter("@ID", FromDetailID));
// purcomm.Parameters.Add(SqlHelper.GetParameter("@RejectCount", CheckNum));
// purcomm.CommandText = pursql;
// sqllist.Add(purcomm);
//}
//#endregion
}
else
{
returnvalue = false;
}
if (BillStatus == "1")
{
if (SqlHelper.ExecuteTransWithArrayList(sqllist))
{
returnvalue = true;
}
else
{
returnvalue = false;
}
}
return returnvalue;
}
/// <summary>
/// 添加不合格品处置单
/// </summary>
/// <param name="model">不合格品处置单 </param>
/// <param name="detail">明细</param>
/// <param name="htExtAttr">扩展属性</param>
/// <returns></returns>
public static bool AddNoPass(CheckNotPassModel model, List<CheckNotPassDetailModel> detail, Hashtable htExtAttr)
{
ArrayList sqllist = new ArrayList();
string sql = "";
#region 主表---Start
sql += "INSERT INTO officedba.CheckNotPass ";
sql += " ([CompanyCD] ";
sql += " ,[ProcessNo] ";
sql += " ,[FromType] ";
sql += " ,[ReportID] ";
sql += " ,[Executor] ";
sql += " ,[ProcessDate] ";
if (!string.IsNullOrEmpty(model.Remark))
{
sql += " ,[Remark] ";
}
sql += " ,[Title] ";
sql += " ,[CreateDate] ";
sql += " ,[BillStatus] ";
sql += " ,[ModifiedDate] ";
sql += " ,[ModifiedUserID] ";
if (!string.IsNullOrEmpty(model.Attachment))
{
sql += " ,[Attachment] ";
}
sql += " ,[Creator]) ";
sql += " VALUES ";
sql += " (@CompanyCD ";
sql += " ,@ProcessNo ";
sql += " ,@FromType ";
sql += " ,@ReportID ";
sql += " ,@Executor ";
sql += " ,@ProcessDate ";
if (!string.IsNullOrEmpty(model.Remark))
{
sql += " ,@Remark ";
}
sql += " ,@Title ";
sql += " ,@CreateDate ";
sql += " ,@BillStatus ";
sql += " ,@ModifiedDate ";
sql += " ,@ModifiedUserID ";
if (!string.IsNullOrEmpty(model.Attachment))
{
sql += " ,@Attachment ";
}
sql += " ,@Creator) ";
SqlCommand comm = new SqlCommand();
comm.CommandText = sql;
comm.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
comm.Parameters.Add(SqlHelper.GetParameter("@ProcessNo", model.ProcessNo));
comm.Parameters.Add(SqlHelper.GetParameter("@FromType", model.FromType));
comm.Parameters.Add(SqlHelper.GetParameter("@ReportID", model.ReportID));
comm.Parameters.Add(SqlHelper.GetParameter("@Executor", model.Executor));
comm.Parameters.Add(SqlHelper.GetParameter("@ProcessDate", model.ProcessDate));
if (!string.IsNullOrEmpty(model.Remark))
{
comm.Parameters.Add(SqlHelper.GetParameter("@Remark", model.Remark));
}
comm.Parameters.Add(SqlHelper.GetParameter("@Title", model.Title));
comm.Parameters.Add(SqlHelper.GetParameter("@CreateDate", model.CreateDate));
comm.Parameters.Add(SqlHelper.GetParameter("@BillStatus", model.BillStatus));
comm.Parameters.Add(SqlHelper.GetParameter("@ModifiedDate", model.ModifiedDate));
comm.Parameters.Add(SqlHelper.GetParameter("@ModifiedUserID", model.ModifiedUserID));
comm.Parameters.Add(SqlHelper.GetParameter("@Creator", model.Creator));
if (!string.IsNullOrEmpty(model.Attachment))
{
comm.Parameters.Add(SqlHelper.GetParameter("@Attachment", model.Attachment));
}
sqllist.Add(comm);
// 更新扩展属性
SqlCommand commExtAttr = UpdateExtAttr(model, htExtAttr);
if (commExtAttr != null)
{
sqllist.Add(commExtAttr);
}
#endregion
#region 明细---Start
CheckNotPassDetailModel detailmodel = new CheckNotPassDetailModel();
if (!string.IsNullOrEmpty(model.ProcessNo))
{
for (int i = 0; i < detail.Count; i++)
{
string detailsql = "";
detailsql += "";
detailsql += " INSERT INTO [officedba].[CheckNotPassDetail] ";
detailsql += " ([CompanyCD] ";
detailsql += " ,[ProcessNo] ";
detailsql += " ,[SortNo] ";
detailsql += " ,[ProductID] ";
detailsql += " ,[UnitID] ";
detailsql += " ,[ReasonID] ";
detailsql += " ,[NotPassNum] ";
detailsql += " ,[ProcessWay] ";
detailsql += " ,[Rate] ";
detailsql += " ,[ModifiedDate] ";
if (!string.IsNullOrEmpty(detail[i].Remark))
{
detailsql += " ,[Remark] ";
}
detailsql += " ,[ModifiedUserID]) ";
detailsql += " VALUES ";
detailsql += " (@CompanyCD ";
detailsql += " ,@ProcessNo ";
detailsql += " ,@SortNo ";
detailsql += " ,@ProductID ";
detailsql += " ,@UnitID ";
detailsql += " ,@ReasonID ";
detailsql += " ,@NotPassNum ";
detailsql += " ,@ProcessWay ";
detailsql += " ,@Rate ";
detailsql += " ,@ModifiedDate ";
if (!string.IsNullOrEmpty(detail[i].Remark))
{
detailsql += " ,@Remark ";
}
detailsql += " ,@ModifiedUserID) ";
SqlCommand detailcomm = new SqlCommand();
detailcomm.CommandText = detailsql;
detailcomm.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
detailcomm.Parameters.Add(SqlHelper.GetParameter("@ProcessNo", model.ProcessNo));
detailcomm.Parameters.Add(SqlHelper.GetParameter("@SortNo", detail[i].SortNo));
detailcomm.Parameters.Add(SqlHelper.GetParameter("@ProductID", detail[i].ProductID));
detailcomm.Parameters.Add(SqlHelper.GetParameter("@UnitID", detail[i].UnitID));
detailcomm.Parameters.Add(SqlHelper.GetParameter("@ReasonID", detail[i].ReasonID));
detailcomm.Parameters.Add(SqlHelper.GetParameter("@NotPassNum", detail[i].NotPassNum));
detailcomm.Parameters.Add(SqlHelper.GetParameter("@ProcessWay", detail[i].ProcessWay));
detailcomm.Parameters.Add(SqlHelper.GetParameter("@Rate", detail[i].Rate));
detailcomm.Parameters.Add(SqlHelper.GetParameter("@ModifiedDate", DateTime.Now));
if (!string.IsNullOrEmpty(detail[i].Remark))
{
detailcomm.Parameters.Add(SqlHelper.GetParameter("@Remark", detail[i].Remark));
}
detailcomm.Parameters.Add(SqlHelper.GetParameter("@ModifiedUserID", model.ModifiedUserID));
sqllist.Add(detailcomm);
}
}
#endregion
if (SqlHelper.ExecuteTransWithArrayList(sqllist))
{
return true;
}
else
{
return false;
}
}
/// <summary>
/// 更新不合格品处置单
/// </summary>
/// <param name="model">不合格品处置单 </param>
/// <param name="detailmodel">明细</param>
/// <param name="SortID"></param>
/// <param name="htExtAttr">扩展属性</param>
/// <returns></returns>
public static bool UpdateNoPassInfo(CheckNotPassModel model, List<CheckNotPassDetailModel> detailmodel, string[] SortID, Hashtable htExtAttr)
{
string loginUserID = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).UserID;//[待修改]
ArrayList sqllist = new ArrayList();
//string loginUserID = "admin123";
if (string.IsNullOrEmpty(model.ProcessNo))
{
return false;
}
#region 基本信息
string sql = "";
sql += " UPDATE [officedba].[CheckNotPass] ";
sql += " SET [FromType] = @FromType ";
sql += " ,[ReportID] = @ReportID ";
sql += " ,[Executor] = @Executor ";
sql += " ,[ProcessDate] = @ProcessDate ";
sql += " ,[BillStatus] = @BillStatus ";
sql += " ,[ModifiedDate] = @ModifiedDate ";
sql += " ,[ModifiedUserID] = @ModifiedUserID ";
sql += " ,[Attachment] = @Attachment ";
sql += " ,[Title] = @Title ";
sql += " ,[Remark] = @Remark ";
sql += " WHERE ID=@ID ";
SqlCommand comm = new SqlCommand();
comm.Parameters.Add(SqlHelper.GetParameter("@FromType", model.FromType));
comm.Parameters.Add(SqlHelper.GetParameter("@ReportID", model.ReportID));
comm.Parameters.Add(SqlHelper.GetParameter("@Executor", model.Executor));
comm.Parameters.Add(SqlHelper.GetParameter("@ProcessDate", model.ProcessDate));
comm.Parameters.Add(SqlHelper.GetParameter("@BillStatus", model.BillStatus));
comm.Parameters.Add(SqlHelper.GetParameter("@ModifiedDate", DateTime.Now));
comm.Parameters.Add(SqlHelper.GetParameter("@ModifiedUserID", loginUserID));
comm.Parameters.Add(SqlHelper.GetParameter("@Attachment", model.Attachment));
comm.Parameters.Add(SqlHelper.GetParameter("@Title", model.Title));
comm.Parameters.Add(SqlHelper.GetParameter("@Remark", model.Remark));
comm.Parameters.Add(SqlHelper.GetParameter("@ID", model.ID));
comm.CommandText = sql;
sqllist.Add(comm);
// 更新扩展属性
SqlCommand commExtAttr = UpdateExtAttr(model, htExtAttr);
if (commExtAttr != null)
{
sqllist.Add(commExtAttr);
}
#endregion
#region 明细
if (SortID.Length > 0)
{
string delsql = "DELETE FROM [officedba].[CheckNotPassDetail] where CompanyCD=@CompanyCD and ProcessNo=@ProcessNo ";
SqlCommand delcomm = new SqlCommand();
delcomm.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
delcomm.Parameters.Add(SqlHelper.GetParameter("@ProcessNo", model.ProcessNo));
delcomm.CommandText = delsql;
sqllist.Add(delcomm);
if (SortID[0] != null && SortID[0] != "")
{
for (int i = 0; i < detailmodel.Count; i++)
{
string detailsql = "";
detailsql += "";
detailsql += " INSERT INTO [officedba].[CheckNotPassDetail] ";
detailsql += " ([CompanyCD] ";
detailsql += " ,[ProcessNo] ";
detailsql += " ,[SortNo] ";
detailsql += " ,[ProductID] ";
detailsql += " ,[UnitID] ";
detailsql += " ,[ReasonID] ";
detailsql += " ,[NotPassNum] ";
detailsql += " ,[ProcessWay] ";
detailsql += " ,[Rate] ";
detailsql += " ,[ModifiedDate] ";
if (!string.IsNullOrEmpty(detailmodel[i].Remark))
{
detailsql += " ,[Remark] ";
}
detailsql += " ,[ModifiedUserID]) ";
detailsql += " VALUES ";
detailsql += " (@CompanyCD ";
detailsql += " ,@ProcessNo ";
detailsql += " ,@SortNo ";
detailsql += " ,@ProductID ";
detailsql += " ,@UnitID ";
detailsql += " ,@ReasonID ";
detailsql += " ,@NotPassNum ";
detailsql += " ,@ProcessWay ";
detailsql += " ,@Rate ";
detailsql += " ,@ModifiedDate ";
if (!string.IsNullOrEmpty(detailmodel[i].Remark))
{
detailsql += " ,@Remark ";
}
detailsql += " ,@ModifiedUserID) ";
SqlCommand detailcomm = new SqlCommand();
detailcomm.CommandText = detailsql;
detailcomm.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
detailcomm.Parameters.Add(SqlHelper.GetParameter("@ProcessNo", model.ProcessNo));
detailcomm.Parameters.Add(SqlHelper.GetParameter("@SortNo", detailmodel[i].SortNo));
detailcomm.Parameters.Add(SqlHelper.GetParameter("@ProductID", detailmodel[i].ProductID));
detailcomm.Parameters.Add(SqlHelper.GetParameter("@UnitID", detailmodel[i].UnitID));
detailcomm.Parameters.Add(SqlHelper.GetParameter("@ReasonID", detailmodel[i].ReasonID));
detailcomm.Parameters.Add(SqlHelper.GetParameter("@NotPassNum", detailmodel[i].NotPassNum));
detailcomm.Parameters.Add(SqlHelper.GetParameter("@ProcessWay", detailmodel[i].ProcessWay));
detailcomm.Parameters.Add(SqlHelper.GetParameter("@Rate", detailmodel[i].Rate));
detailcomm.Parameters.Add(SqlHelper.GetParameter("@ModifiedDate", DateTime.Now));
if (!string.IsNullOrEmpty(detailmodel[i].Remark))
{
detailcomm.Parameters.Add(SqlHelper.GetParameter("@Remark", detailmodel[i].Remark));
}
detailcomm.Parameters.Add(SqlHelper.GetParameter("@ModifiedUserID", model.ModifiedUserID));
sqllist.Add(detailcomm);
}
}
}
#endregion
if (SqlHelper.ExecuteTransWithArrayList(sqllist))
{
return true;
}
else
{
return false;
}
}
public static DataTable GetNoPassDetail(CheckNotPassModel model)
{
string sql = "";
sql += " select * from (SELECT [ProcessNo],ID ";
sql += " ,[SortNo] ";
sql += " ,[ProductID] ";
sql += " ,[UnitID] ";
sql += " ,[ReasonID] ";
sql += " ,convert(numeric(12,2),isnull(NotPassNum,0)) NotPassNum ";
sql += " ,[ProcessWay] ";
sql += " ,convert(numeric(12,2),isnull(Rate,0)) as Rate ";
sql += " ,isnull([Remark],'') as Remark ";
sql += " FROM [officedba].[CheckNotPassDetail] ";
sql += " where [officedba].[CheckNotPassDetail].ProcessNo=(select top 1 ProcessNo from [officedba].[CheckNotPass] where CompanyCD=@CompanyCD and ID=@ID)) as Info ";
//定义查询的命令
SqlCommand comm = new SqlCommand();
//添加公司代码参数
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ID", model.ID.ToString()));
//指定命令的SQL文
comm.CommandText = sql.ToString();
//执行查询
return SqlHelper.ExecuteSearch(comm);
}
public static DataTable GetNoPassInfo(CheckNotPassModel model)
{
int BillTypeFlag = int.Parse(ConstUtil.CODING_RULE_StorageQuality_NO);
int BillTypeCode = int.Parse(ConstUtil.CODING_RULE_StorageNOPass_NO);
string sql = "";
sql += " SELECT isnull(c.[ProcessNo],'') as ProcessNo ";
sql += " ,c.[FromType],c.ID ";
sql += " ,c.[ReportID] ";
sql += " ,c.[Executor] ";
sql += " ,Replace(c.[Attachment],'\\',',') as Attachment ";
sql += " ,isnull(CONVERT(varchar,c.ProcessDate,120),'') as [ProcessDate] ";
sql += " ,isnull(c.[Remark],'') as Remark ";
sql += " ,isnull(CONVERT(varchar,c.CreateDate,120),'') as [CreateDate] ";
sql += " ,isnull(f.FlowStatus,'0') as FlowStatus";
sql += " ,c.[BillStatus] ";
sql += " ,c.[Confirmor] ";
sql += " ,isnull(CONVERT(varchar,c.ConfirmorDate,120),'') as [ConfirmorDate] ";
sql += " ,c.[Closer]";
sql += " ,q.ProductID ";
sql += " ,isnull(CONVERT(varchar,c.CloserDate,120),'') as [CloserDate] ";
sql += " ,isnull(CONVERT(varchar,c.ModifiedDate,120),'') as [ModifiedDate] ";
sql += " ,(c.ModifiedUserID) as ModifiedUserIDName";
sql += " ,c.[ModifiedUserID] ";
sql += " ,(select EmployeeName from officedba.EmployeeInfo where officedba.EmployeeInfo.ID=c.Closer) as CloserName";
sql += " ,(select EmployeeName from officedba.EmployeeInfo where officedba.EmployeeInfo.ID=c.Confirmor) as ConfirmorName";
sql += " ,(select EmployeeName from officedba.EmployeeInfo where officedba.EmployeeInfo.ID=c.Creator) as CreatorName";
sql += " ,isnull(c.[Creator],0) as Creator ";
sql += " ,isnull(c.[Title],'')as Title ";
sql += " ,isnull(q.ReportNo,'') as ReportNo ";
sql += " ,isnull(e.EmployeeName,'') as EmployeeName ";
sql += " ,isnull((select ProductName from officedba.ProductInfo as p1 where p1.ID=q.ProductID),'')as ProductName ";
sql += " ,isnull((select ProdNo from officedba.ProductInfo as p1 where p1.ID=q.ProductID),'')as ProNo ";
sql += " ,isnull((select Specification from officedba.ProductInfo as p1 where p1.ID=q.ProductID),'')as Specification ";
sql += " ,isnull((select CodeName from officedba.CodeUnitType where officedba.CodeUnitType.ID=(select UnitID from officedba.ProductInfo as p1 where p1.ID=q.ProductID)),'')as UnitName ";
sql += " ,isnull((select UnitID from officedba.ProductInfo as p1 where p1.ID=q.ProductID),0) as UnitID";
sql += " ,convert(numeric(12,2),isnull(q.NoPass,0)) as NoPass ";
sql += " ,c.ExtField1,c.ExtField2,c.ExtField3,c.ExtField4,c.ExtField5,c.ExtField6,c.ExtField7,c.ExtField8,c.ExtField9,c.ExtField10 ";
sql += " FROM [officedba].[CheckNotPass] as c left join officedba.QualityCheckReport as q on c.ReportID=q.ID ";
sql += " left join officedba.EmployeeInfo as e on c.Executor=e.ID ";
sql += " left join officedba.FlowInstance AS f ON c.CompanyCD = f.CompanyCD AND f.BillTypeFlag = " + BillTypeFlag + " AND f.BillTypeCode = " + BillTypeCode + " AND c.ID = f.BillID ";
sql += " where c.CompanyCD=@CompanyCD and q.CompanyCD=@CompanyCD and c.ID=@ID ";
//定义查询的命令
SqlCommand comm = new SqlCommand();
//添加公司代码参数
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ID", model.ID.ToString()));
//指定命令的SQL文
comm.CommandText = sql.ToString();
//执行查询
return SqlHelper.ExecuteSearch(comm);
}
/// <summary>
/// 取消确认
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static bool UnConfirmBill(CheckNotPassModel model, string CheckNum)
{
return CheckNotPassDBHelper.UnConfirmBill(model, CheckNum);
}
public static bool CloseBill(CheckNotPassModel model, string method)
{
ArrayList listsql = new ArrayList();
SqlCommand comm = new SqlCommand();
StringBuilder sql = new StringBuilder();
sql.AppendLine(" UPDATE officedba.CheckNotPass SET");
sql.AppendLine(" BillStatus =@BillStatus,");
sql.AppendLine(" ModifiedUserID = @ModifiedUserID,");
if (method == "0")
{
sql.AppendLine(" Closer = @Closer,");
sql.AppendLine(" CloserDate = @CloserDate, ");
}
sql.AppendLine(" ModifiedDate = @ModifiedDate");
sql.AppendLine(" Where CompanyCD=@CompanyCD and ID=@ID");
comm.Parameters.Add(SqlHelper.GetParameter("@ID", model.ID));
comm.Parameters.Add(SqlHelper.GetParameter("@ModifiedUserID", model.ModifiedUserID));
comm.Parameters.Add(SqlHelper.GetParameter("@ModifiedDate", model.ModifiedDate));
comm.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
string BillStatus = "2";
if (method == "0")
{
comm.Parameters.Add(SqlHelper.GetParameter("@Closer", model.Closer));
comm.Parameters.Add(SqlHelper.GetParameter("@CloserDate", model.CloserDate));
BillStatus = "4";
}
comm.Parameters.Add(SqlHelper.GetParameter("@BillStatus", BillStatus));
comm.CommandText = sql.ToString();
listsql.Add(comm);
if (SqlHelper.ExecuteTransWithArrayList(listsql))
{ return true; }
else
{ return false; }
}
public static string GetNotPassNum(CheckNotPassModel model)
{
return CheckNotPassDBHelper.GetNoExecutNum(model);
}
public static string GetNoExecutNum(CheckNotPassModel model)
{
SqlCommand comm = new SqlCommand();
string sql = "";
sql += " select isnull(a.NoPass,0) as NoPass, ";
sql += " isnull((select isnull(sum(NotPassNum),0) from officedba.CheckNotPassDetail as m left join officedba.CheckNotPass as n on m.ProcessNo=n.ProcessNo where n.BillStatus='2' and m.CompanyCD=@CompanyCD and n.ReportID=@ReportID),0) as NotPassNum";
sql += " from officedba.QualityCheckReport as a ";
sql += " where a.CompanyCD=@CompanyCD and a.ID=@ID ";
comm.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
comm.Parameters.Add(SqlHelper.GetParameter("@ID", model.ReportID));
comm.Parameters.Add(SqlHelper.GetParameter("@ReportID", model.ReportID));
comm.CommandText = sql;
DataTable dt = SqlHelper.ExecuteSearch(comm);
string NoPass = "******";
string NotPassNum = "0";
if (dt.Rows.Count > 0)
{
NoPass = dt.Rows[0]["NoPass"].ToString();
NotPassNum = dt.Rows[0]["NotPassNum"].ToString();
}
string value = "0";
if (NoPass != "" && NotPassNum != "")
{
value = Convert.ToString(Convert.ToDecimal(NoPass) - Convert.ToDecimal(NotPassNum));
}
return value;
}
public static DataTable SearchNoPass(CheckNotPassModel model, string ProductID, string BeginTime, string EndTime, string FlowStatus, string EFIndex, string EFDesc, ref int TotalCount)
{
return CheckNotPassDBHelper.GetAllNoPass(model, ProductID, BeginTime, EndTime, FlowStatus,EFIndex,EFDesc, ref TotalCount);
}
/// <summary>
/// 扩展属性更新命令
/// </summary>
/// <param name="model">不合格品处置单</param>
/// <param name="htExtAttr">扩展属性</param>
/// <returns></returns>
public static SqlCommand UpdateExtAttr(CheckNotPassModel model, Hashtable htExtAttr)
{
SqlCommand sqlcomm = new SqlCommand();
if (htExtAttr == null || htExtAttr.Count < 1)
{// 没有属性需要修改
return null;
}
StringBuilder sb = new StringBuilder(" UPDATE officedba.CheckNotPass SET ");
foreach (DictionaryEntry de in htExtAttr)// fileht为一个Hashtable实例
{
sb.AppendFormat(" {0}=@{0},", de.Key.ToString());
sqlcomm.Parameters.Add(SqlHelper.GetParameter(String.Format("@{0}", de.Key.ToString()), de.Value));
}
string strSql = sb.ToString();
strSql = strSql.TrimEnd(',');
strSql += " WHERE CompanyCD = @CompanyCD AND ProcessNo = @ProcessNo ";
sqlcomm.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
sqlcomm.Parameters.Add(SqlHelper.GetParameter("@ProcessNo", model.ProcessNo));
sqlcomm.CommandText = strSql;
return sqlcomm;
}