/// <summary>
/// 添加质检
/// </summary>
/// <param name="model"></param>
/// <param name="detailList"></param>
/// <returns></returns>
public static bool AddQualityCheck(StorageQualityCheckApplay model, List<StorageQualityCheckApplyDetail> detailList, Hashtable htExtAttr)
{
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
try
{
bool result = false;
LogInfoModel logModel = InitLogInfo(model.ApplyNO);
logModel.Element = ConstUtil.LOG_PROCESS_INSERT;
result = StorageQualityCheckProDBHelper.AddStoryQualityCheckDB(model, detailList, htExtAttr);
if (!result)
{
logModel.Remark = ConstUtil.LOG_PROCESS_FAILED;
}
else
{
logModel.Remark = ConstUtil.LOG_PROCESS_SUCCESS;
model.ID = IDIdentityUtil.GetIDIdentity("officedba.QualityCheckApplay");
}
LogDBHelper.InsertLog(logModel);
return result;
}
catch (Exception ex)
{
WriteSystemLog(userInfo, ex);
return false;
}
}
public static bool UpdateQualityCheck(StorageQualityCheckApplay model, List<StorageQualityCheckApplyDetail> DetailList, string[] ProductID, Hashtable htExtAttr)
{
//return StorageQualityCheckProDBHelper.UpdateStorageCheck(model, DetailList, ProductID);
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
try
{
bool succ = false;
LogInfoModel logModel = InitLogInfo(model.ApplyNO);
logModel.Element = ConstUtil.LOG_PROCESS_UPDATE;
succ = StorageQualityCheckProDBHelper.UpdateStorageCheck(model, DetailList, ProductID, 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;
}
}
/// <summary>
///
/// </summary>
/// <param name="CheckedCount">明细报检数量</param>
/// <param name="ID">源单ID</param>
/// <param name="FromType">源单类型</param>
/// <param name="ModifiedUserID">最后更新人</param>
/// <param name="CompanyCD"></param>
/// <param name="QuaID">质检主表ID</param>
/// <param name="ApplyNo">质检编号</param>
/// <returns></returns>
public static bool UpPurDetail(string CheckedCount, string ID, string FromType, string ModifiedUserID, string CompanyCD, StorageQualityCheckApplay model)
{
string sql = "";
bool returnvalue = false;
ArrayList sqllist = new ArrayList();
int BillTypeFlag = int.Parse(ConstUtil.BILL_TYPECODE_STORAGE_QUALITY);
int BillTypeCode = int.Parse(ConstUtil.BILL_TYPECODE_STORAGE_QUALITYADD);
// 通过明细ID获取主表的编号
SqlCommand PurComm = new SqlCommand();
string PurSql = "select BillStatus from officedba.QualityCheckApplay where ApplyNo=@ApplyNo and CompanyCD=@CompanyCD";
PurComm.Parameters.Add(SqlHelper.GetParameter("@ApplyNo", model.ApplyNO));
PurComm.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", CompanyCD));
PurComm.CommandText = PurSql;
DataTable PurDt = SqlHelper.ExecuteSearch(PurComm);
string BillStatus = "0";
if (PurDt.Rows.Count > 0)
{
BillStatus = PurDt.Rows[0]["BillStatus"].ToString();
}
if (BillStatus == "2")
{
if (FromType == "1") //采购
{
string[] myID = ID.Split(',');
string[] myCount = CheckedCount.Split(',');
for (int i = 0; i < myID.Length; i++)
{
if (BillStatus == "2")
{
SqlCommand comm = new SqlCommand();
sql = "update officedba.PurchaseArriveDetail set ApplyCheckCount=isnull(ApplyCheckCount,0)-@ApplyCheckCount where ID=@ID";
comm.Parameters.Add(SqlHelper.GetParameter("@ID", myID[i].ToString()));
comm.Parameters.Add(SqlHelper.GetParameter("@ApplyCheckCount", myCount[i].ToString()));
comm.CommandText = sql;
sqllist.Add(comm);
}
}
}
else if (FromType == "2")// 生产任务单
{
string[] myID = ID.Split(',');
string[] myCount = CheckedCount.Split(',');
for (int i = 0; i < myID.Length; i++)
{
SqlCommand detailcomm = new SqlCommand();
sql = " update officedba.ManufactureTaskDetail set ApplyCheckCount=isnull(ApplyCheckCount,0)-@ApplyCheckCount where ID=@ID";
detailcomm.Parameters.Add(SqlHelper.GetParameter("@ID", myID[i].ToString()));
detailcomm.Parameters.Add(SqlHelper.GetParameter("@ApplyCheckCount", myCount[i].ToString()));
detailcomm.CommandText = sql;
sqllist.Add(detailcomm);
}
}
#region 撤消审批流程
DataTable dtFlowInstance = Common.FlowDBHelper.GetFlowInstanceInfo(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", 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", 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", CompanyCD));
commTask.Parameters.Add(SqlHelper.GetParameter("@tempFlowInstanceID", FlowInstanceID));
commTask.Parameters.Add(SqlHelper.GetParameter("@ModifiedUserID", 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", 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", ModifiedUserID));
sqllist.Add(commIns);
#endregion
}
#endregion
#region 更新质检主表
SqlCommand QuaComm = new SqlCommand();
string QuaSql = "update officedba.QualityCheckApplay set BillStatus='1',ModifiedUserID=@ModifiedUserID,ModifiedDate=@ModifiedDate";
QuaSql += " where ApplyNo=@ApplyNo and CompanyCD=@CompanyCD";
QuaComm.Parameters.Add(SqlHelper.GetParameter("@ModifiedUserID", ModifiedUserID));
QuaComm.Parameters.Add(SqlHelper.GetParameter("@ModifiedDate", DateTime.Now));
QuaComm.Parameters.Add(SqlHelper.GetParameter("@ApplyNo", model.ApplyNO));
QuaComm.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", CompanyCD));
QuaComm.CommandText = QuaSql;
sqllist.Add(QuaComm);
#endregion
#region 更新质检明细 已检数量
SqlCommand QuaDetailComm = new SqlCommand();
string QuaDetailSql = "update officedba.QualityCheckApplyDetail set CheckedCount=isnull(CheckedCount,0)-isnull(ProductCount,0)";
QuaDetailSql += " where ApplyNo=@ApplyNo and CompanyCD=@CompanyCD";
QuaDetailComm.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", CompanyCD));
QuaDetailComm.Parameters.Add(SqlHelper.GetParameter("@ApplyNo", model.ApplyNO));
QuaDetailComm.CommandText = QuaDetailSql;
sqllist.Add(QuaDetailComm);
#endregion
}
else
{
returnvalue = false;
}
if (BillStatus == "2")
{
if (SqlHelper.ExecuteTransWithArrayList(sqllist))
{
returnvalue = true;
}
else
{
returnvalue = false;
}
}
return returnvalue;
}
/// <summary>
/// 获取质检列表
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static DataTable GetQualityCheckList(StorageQualityCheckApplay model, DateTime EndCheckDate, string FlowStatus, string EFIndex, string EFDesc, ref int TotalCount)
{
StringBuilder sql = new StringBuilder();
int BillTypeFlag = int.Parse(ConstUtil.BILL_TYPECODE_STORAGE_QUALITY);
int BillTypeCode = int.Parse(ConstUtil.BILL_TYPECODE_STORAGE_QUALITYADD);
sql.AppendLine(" select * from ( ");
sql.AppendLine("SELECT a.ID,a.ModifiedDate,isnull(a.BillStatus,'') as BillStatusID,isnull(e.FlowStatus,'0') as FlowStatusID,a.ApplyNo,isnull(a.Title,'') as Title,isnull(a.CustName,'') as CustName,isnull(b.EmployeeName,'') as EmployeeName,isnull(c.DeptName,'') as DeptName,isnull(substring(CONVERT(varchar,a.CheckDate,120),0,11),'') as CheckDate,isnull(a.FromType,'') as FromTypeID, ");
sql.AppendLine(" isnull(CASE a.FromType WHEN '0' THEN '无来源' WHEN '1' THEN '采购到货单' WHEN '2' THEN '生产任务单' END,'') AS FromType, ");
sql.AppendLine(" isnull(CASE a.CheckType WHEN '1' THEN '进货检验' WHEN '2' THEN '过程检验' WHEN '3' THEN '最终检验' END,'') AS CheckType,");
sql.AppendLine(" isnull(CASE a.CheckMode WHEN '1' THEN '全检' WHEN '2' THEN '抽检' WHEN '3' THEN '临检' END,'') AS CheckMode, ");
sql.AppendLine(" isnull(CASE a.BillStatus WHEN '1' THEN '制单' WHEN '2' THEN '执行' WHEN '3' THEN '变更' WHEN '4' THEN '手工结单' WHEN '5' THEN '自动结单' END,'') AS BillStatus,");
sql.AppendLine(" isnull(CASE e.FlowStatus WHEN '1' THEN '待审批' WHEN '2' THEN '审批中' WHEN '3' THEN '审批通过' WHEN '4' THEN '审批不通过' when '5' then '撤消审批' else '' END,'') AS FlowStatus, ");
sql.AppendLine(" isnull(CASE a.CustBigType WHEN '1' THEN '客户' WHEN '2' THEN '供应商' WHEN '3' THEN '竞争对手' WHEN '4' THEN '银行' WHEN '5' THEN '外协加工厂' WHEN '6' THEN '运输商' WHEN '7' THEN '其他' END,'') AS CustBigType ");
sql.AppendLine("FROM officedba.QualityCheckApplay AS a left JOIN");
sql.AppendLine(" officedba.EmployeeInfo AS b ON a.Checker = b.ID AND a.CompanyCD = b.CompanyCD LEFT JOIN");
sql.AppendLine(" officedba.DeptInfo AS c ON a.CheckDeptId = c.ID AND a.CompanyCD = c.CompanyCD LEFT JOIN");
sql.AppendLine(" officedba.CustInfo AS d ON a.CustID = d.ID AND a.CompanyCD = d.CompanyCD LEFT JOIN");
sql.AppendLine(" officedba.FlowInstance AS e ON a.CompanyCD = e.CompanyCD AND e.BillTypeFlag =" + BillTypeFlag + " AND e.BillTypeCode = " + BillTypeCode + " AND a.ID = e.BillID");
sql.AppendLine(" and e.ID=(select max(ID) from officedba.FlowInstance where a.CompanyCD = officedba.FlowInstance.CompanyCD AND officedba.FlowInstance.BillTypeFlag = " + BillTypeFlag + " AND officedba.FlowInstance.BillTypeCode = " + BillTypeCode + " AND a.ID = officedba.FlowInstance.BillID) ");
sql.AppendLine("WHERE 1=1 ");
SqlCommand comm = new SqlCommand();
if (model.FromType != "00" && model.FromType != null)
{
sql.AppendLine(" and a.FromType=@FromType");
comm.Parameters.Add(SqlHelper.GetParameter("@FromType", model.FromType));
}
if (model.ApplyNO != null && model.ApplyNO != "")
{
sql.AppendLine(" and a.ApplyNo like @ApplyNO");
comm.Parameters.Add(SqlHelper.GetParameter("@ApplyNO", "%" + model.ApplyNO + "%"));
}
if (model.Title != null && model.Title != "")
{
sql.AppendLine(" and a.Title like @Title ");
comm.Parameters.Add(SqlHelper.GetParameter("@Title", "%" + model.Title + "%"));
}
if (model.Checker != "0" && model.Checker != "" && model.Checker != null)
{
sql.AppendLine(" and a.Checker=@Checker");
comm.Parameters.Add(SqlHelper.GetParameter("@Checker", model.Checker));
}
if (!string.IsNullOrEmpty(model.CustName) && model.CustName != "0")
{
sql.AppendLine(" and a.CustID = @CustID ");
comm.Parameters.Add(SqlHelper.GetParameter("@CustID", model.CustName));
}
if (model.CheckDeptID != "0" && model.CheckDeptID != "" && model.CheckDeptID != null)
{
sql.AppendLine(" and a.CheckDeptId=@CheckDeptID");
comm.Parameters.Add(SqlHelper.GetParameter("@CheckDeptID", model.CheckDeptID));
}
if (model.CheckDate != Convert.ToDateTime("1800-1-1")) //起始日期
{
sql.AppendLine(" and a.CheckDate >=@CheckDate");
comm.Parameters.Add(SqlHelper.GetParameter("@CheckDate", model.CheckDate));
}
if (EndCheckDate != Convert.ToDateTime("9999-2-3")) //终止日期
{
sql.AppendLine(" and a.CheckDate <= @EndDate");
comm.Parameters.Add(SqlHelper.GetParameter("@EndDate", EndCheckDate));
}
if (model.CheckType != "00" && model.CheckType != null)
{
sql.AppendLine(" and a.CheckType=@CheckType ");
comm.Parameters.Add(SqlHelper.GetParameter("@CheckType", model.CheckType));
}
if (model.CheckMode != "00" && model.CheckMode != null)
{
sql.AppendLine(" and a.CheckMode=@CheckMode ");
comm.Parameters.Add(SqlHelper.GetParameter("@CheckMode", model.CheckMode));
}
if (!string.IsNullOrEmpty(EFIndex) && !string.IsNullOrEmpty(EFDesc))
{
sql.AppendLine(" and a.ExtField" + EFIndex + " LIKE @EFDesc ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EFDesc", "%" + EFDesc + "%"));
}
if (model.BillStatus != "00" && model.BillStatus != null)
{
sql.AppendLine(" and a.BillStatus=@BillStatus");
comm.Parameters.Add(SqlHelper.GetParameter("@BillStatus", model.BillStatus));
}
if (FlowStatus != null && FlowStatus != "00" && FlowStatus != "6")
{
sql.AppendLine(" and e.FlowStatus=@FlowStatus");
comm.Parameters.Add(SqlHelper.GetParameter("@FlowStatus", FlowStatus));
}
sql.AppendLine(" and a.CompanyCD=@CompanyCD ");
comm.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
sql.AppendLine(" ) as Info ");
if (FlowStatus == "6")
{
sql.AppendLine(" where FlowStatusID='0'");
}
if (model.Creater == -100)//用这个属性来存储排序信息
{
sql.AppendLine(" order by " + model.Attachment);
}
comm.CommandText = sql.ToString();
DataTable dt = new DataTable();
if (model.Creater == -100)
{
dt = SqlHelper.ExecuteSearch(comm);
}
else
{
dt = SqlHelper.PagerWithCommand(comm, model.Creater, model.Confirmor, model.Attachment, ref TotalCount);
}
return dt;
}
/// <summary>
/// 确认质检
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static bool ConfirmBill(StorageQualityCheckApplay model)
{
ArrayList sqllist = new ArrayList();
StringBuilder sql = new StringBuilder();
SqlCommand comm = new SqlCommand();
sql.AppendLine(" UPDATE officedba.QualityCheckApplay SET");
sql.AppendLine(" Confirmor = @Confirmor,");
sql.AppendLine(" ConfirmDate = @ConfirmDate,");
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("@ConfirmDate", model.ConfirmDate));
comm.Parameters.Add(SqlHelper.GetParameter("@ModifiedUserID", model.ModifiedUserID));
comm.Parameters.Add(SqlHelper.GetParameter("@ModifiedDate", model.MdodifiedDate));
comm.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD.Trim()));
comm.CommandText = sql.ToString();
sqllist.Add(comm);
SqlCommand DetailComm = new SqlCommand();
string DetailSql = "update officedba.QualityCheckApplyDetail set CheckedCount=isnull(CheckedCount,0)+ProductCount where ApplyNo=@ApplyNo and CompanyCD=@CompanyCD";
DetailComm.Parameters.Add(SqlHelper.GetParameter("@ApplyNo", model.ApplyNO));
DetailComm.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
DetailComm.CommandText = DetailSql;
sqllist.Add(DetailComm);
return SqlHelper.ExecuteTransWithArrayList(sqllist);
}
/// <summary>
/// 扩展属性更新命令
/// </summary>
/// <param name="model">质检申请单</param>
/// <param name="htExtAttr">扩展属性</param>
/// <returns></returns>
public static SqlCommand UpdateExtAttr(StorageQualityCheckApplay model, Hashtable htExtAttr)
{
SqlCommand sqlcomm = new SqlCommand();
if (htExtAttr == null || htExtAttr.Count < 1)
{// 没有属性需要修改
return null;
}
StringBuilder sb = new StringBuilder(" UPDATE officedba.QualityCheckApplay 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 ApplyNO = @ApplyNO ";
sqlcomm.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
sqlcomm.Parameters.Add(SqlHelper.GetParameter("@ApplyNO", model.ApplyNO));
sqlcomm.CommandText = strSql;
return sqlcomm;
}
/// <summary>
/// 质检更新
/// </summary>
/// <param name="model"></param>
/// <param name="detailList"></param>
/// <param name="ProductID"></param>
/// <param name="htExtAttr">扩展属性</param>
/// <returns></returns>
public static bool UpdateStorageCheck(StorageQualityCheckApplay model, List<StorageQualityCheckApplyDetail> detailList, string[] ProductID, Hashtable htExtAttr)
{
string loginUserID = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).UserID;//[待修改]
ArrayList sqllist = new ArrayList();
#region 质检
SqlCommand sqlcomm = new SqlCommand();
StringBuilder sql = new StringBuilder();
sql.AppendLine("update officedba.QualityCheckApplay");
sql.AppendLine(" set CompanyCD=@CompanyCD ");
sql.AppendLine(" ,ApplyNo=@ApplyNo ");
sql.AppendLine(" ,Title=@Title ");
sql.AppendLine(" ,FromType=@FromType ");
sql.AppendLine(" ,CheckDeptId=@CheckDeptId ");
sql.AppendLine(" ,CheckType=@CheckType ");
sql.AppendLine(" ,CheckMode=@CheckMode ");
sql.AppendLine(" ,Checker=@Checker ");
sql.AppendLine(" ,CheckDate=@CheckDate ");
sql.AppendLine(" ,Creator=@Creator ");
sql.AppendLine(" ,CreateDate=@CreateDate ");
sql.AppendLine(" ,BillStatus=@BillStatus ");
sql.AppendLine(" ,Remark=@Remark");
sql.AppendLine(" ,Attachment=@Attachment");
sql.AppendLine(" ,CustID=@CustID ");
sql.AppendLine(" ,CustName=@CustName");
sql.AppendLine(" ,CustBigType=@CustBigType ");
sql.AppendLine(" ,Principal=@Principal");
sql.AppendLine(" ,CountTotal=@CountTotal ");
sql.AppendLine(" ,DeptID=@DeptID");
sql.AppendLine(" ,ModifiedDate=@ModifiedDate");
sql.AppendLine(" ,ModifiedUserID=@ModifiedUserID");
sql.AppendLine(" where ID=@ID");
sqlcomm.Parameters.Add(SqlHelper.GetParameter("@ID", model.ID));
sqlcomm.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
sqlcomm.Parameters.Add(SqlHelper.GetParameter("@ApplyNo", model.ApplyNO));
sqlcomm.Parameters.Add(SqlHelper.GetParameter("@Title", model.Title));
sqlcomm.Parameters.Add(SqlHelper.GetParameter("@FromType", model.FromType));
sqlcomm.Parameters.Add(SqlHelper.GetParameter("@CheckDeptId", model.CheckDeptID));
sqlcomm.Parameters.Add(SqlHelper.GetParameter("@CheckType", model.CheckType));
sqlcomm.Parameters.Add(SqlHelper.GetParameter("@CheckMode", model.CheckMode));
sqlcomm.Parameters.Add(SqlHelper.GetParameter("@Checker", model.Checker));
if (model.CheckDate != Convert.ToDateTime("9999-09-09"))
{
sqlcomm.Parameters.Add(SqlHelper.GetParameter("@CheckDate", model.CheckDate));
}
else
{
sqlcomm.Parameters.Add(SqlHelper.GetParameter("@CheckDate", System.Data.SqlTypes.SqlDateTime.Null));
} sqlcomm.Parameters.Add(SqlHelper.GetParameter("@Creator", model.Creater));
sqlcomm.Parameters.Add(SqlHelper.GetParameter("@CreateDate", DateTime.Now.ToShortDateString()));
sqlcomm.Parameters.Add(SqlHelper.GetParameter("@BillStatus", model.BillStatus));
sqlcomm.Parameters.Add(SqlHelper.GetParameter("@Remark", model.Remark));
sqlcomm.Parameters.Add(SqlHelper.GetParameter("@Attachment", model.Attachment));
sqlcomm.Parameters.Add(SqlHelper.GetParameter("@CustID", model.CustID));
sqlcomm.Parameters.Add(SqlHelper.GetParameter("@CustName", model.CustName));
sqlcomm.Parameters.Add(SqlHelper.GetParameter("@CustBigType", model.CustBigType));
sqlcomm.Parameters.Add(SqlHelper.GetParameter("@Principal", model.Principal));
sqlcomm.Parameters.Add(SqlHelper.GetParameter("@CountTotal", model.CountTotal));
sqlcomm.Parameters.Add(SqlHelper.GetParameter("@DeptID", model.DeptID));
sqlcomm.Parameters.Add(SqlHelper.GetParameter("@ModifiedDate", DateTime.Now.ToShortDateString()));
sqlcomm.Parameters.Add(SqlHelper.GetParameter("@ModifiedUserID", model.ModifiedUserID));
sqlcomm.CommandText = sql.ToString();
sqllist.Add(sqlcomm);
// 更新扩展属性
SqlCommand commExtAttr = UpdateExtAttr(model, htExtAttr);
if (commExtAttr != null)
{
sqllist.Add(commExtAttr);
}
#endregion
#region 明细
string delsql = "delete officedba.QualityCheckApplyDetail where CompanyCD=@CompanyCD and ApplyNo=@ApplyNo ";
SqlCommand delcomm = new SqlCommand();
delcomm.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
delcomm.Parameters.Add(SqlHelper.GetParameter("@ApplyNo", model.ApplyNO));
delcomm.CommandText = delsql;
sqllist.Add(delcomm);
for (int i = 0; i < detailList.Count; i++)
{
StringBuilder cmdsql = new StringBuilder();
cmdsql.AppendLine("Insert into officedba.QualityCheckApplyDetail");
cmdsql.AppendLine("(CompanyCD,ApplyNo,SortNo,ProductID,UnitID,ProductCount");
if (!string.IsNullOrEmpty(detailList[i].Remark))
{
cmdsql.AppendLine(",Remark");
}
if (detailList[i].RealCheckedCount != 0)
{
cmdsql.AppendLine(",RealCheckedCount");
}
cmdsql.AppendLine(",FromType,FromBillID,FromLineNo,CheckedCount");
cmdsql.AppendLine(",UsedUnitID,UsedUnitCount,ExRate)");
cmdsql.AppendLine("values (@CompanyCD,@ApplyNO,@SortNo");
cmdsql.AppendLine(",@ProductID ,@UnitID,@ProductCount");
if (!string.IsNullOrEmpty(detailList[i].Remark))
{
cmdsql.AppendLine(",@Remark");
}
cmdsql.AppendLine(",@FromType,@FromBillID");
cmdsql.AppendLine(",@FromLineNo,@CheckedCount");
cmdsql.AppendLine(",@UsedUnitID,@UsedUnitCount,@ExRate)");
SqlCommand detailcomm = new SqlCommand();
detailcomm.CommandText = cmdsql.ToString();
detailcomm.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
detailcomm.Parameters.Add(SqlHelper.GetParameter("@ApplyNO", model.ApplyNO));
detailcomm.Parameters.Add(SqlHelper.GetParameter("@SortNo", detailList[i].SortNo));
detailcomm.Parameters.Add(SqlHelper.GetParameter("@ProductID", detailList[i].ProductID));
detailcomm.Parameters.Add(SqlHelper.GetParameter("@UnitID", detailList[i].UnitID));
detailcomm.Parameters.Add(SqlHelper.GetParameter("@ProductCount", detailList[i].ProductCount));
if (!string.IsNullOrEmpty(detailList[i].Remark))
{
detailcomm.Parameters.Add(SqlHelper.GetParameter("@Remark", detailList[i].Remark));
}
detailcomm.Parameters.Add(SqlHelper.GetParameter("@FromType", model.FromType));
detailcomm.Parameters.Add(SqlHelper.GetParameter("@FromBillID", detailList[i].FromBillID));
detailcomm.Parameters.Add(SqlHelper.GetParameter("@FromLineNo", detailList[i].FromLineNo));
detailcomm.Parameters.Add(SqlHelper.GetParameter("@CheckedCount", detailList[i].CheckedCount));
if (detailList[i].UsedUnitID.HasValue)
{
detailcomm.Parameters.Add(new SqlParameter("@UsedUnitID", detailList[i].UsedUnitID.Value));
}
else
{
detailcomm.Parameters.Add(new SqlParameter("@UsedUnitID", DBNull.Value));
}
if (detailList[i].UsedUnitCount.HasValue)
{
detailcomm.Parameters.Add(new SqlParameter("@UsedUnitCount", detailList[i].UsedUnitCount.Value));
}
else
{
detailcomm.Parameters.Add(new SqlParameter("@UsedUnitCount", DBNull.Value));
}
if (detailList[i].ExRate.HasValue)
{
detailcomm.Parameters.Add(SqlHelper.GetParameter("@ExRate", detailList[i].ExRate.Value));
}
else
{
detailcomm.Parameters.Add(SqlHelper.GetParameter("@ExRate", DBNull.Value));
}
detailcomm.CommandText = cmdsql.ToString();
sqllist.Add(detailcomm);
}
#endregion
try
{
return SqlHelper.ExecuteTransWithArrayList(sqllist);
}
catch (Exception ex)
{
throw ex;
}
}
public static bool GetCheckSaveCount(StorageQualityCheckApplay model, string IDList, string TheCheckedCount)
{
ArrayList sqllist = new ArrayList();
bool returnvalue = true;
string[] myID = IDList.Split(',');
string[] myCheckedCount = TheCheckedCount.Split(',');
if (model.FromType == "1")//采购
{
for (int i = 0; i < myID.Length; i++)
{
string ProductCount = "00";
string CheckedCount = "00";
string CheckedCount1 = "00";
string pursql = "select ProductCount,ApplyCheckCount as CheckedCount,isnull(ProductCount,0)-isnull(ApplyCheckCount,0) as CheckedCount1 from officedba.PurchaseArriveDetail as a where a.ID=@ID";
SqlCommand purcomm = new SqlCommand();
purcomm.Parameters.Add(SqlHelper.GetParameter("@ID", myID[i]));
purcomm.CommandText = pursql;
DataTable dt = SqlHelper.ExecuteSearch(purcomm);
if (dt.Rows.Count > 0)
{
ProductCount = dt.Rows[0]["ProductCount"].ToString(); //到货数量
CheckedCount = dt.Rows[0]["CheckedCount"].ToString();//已报检数量
CheckedCount1 = dt.Rows[0]["CheckedCount1"].ToString();//未报检数量
if (ProductCount != "" && CheckedCount != "")
{
if (Convert.ToDecimal(ProductCount) == Convert.ToDecimal(CheckedCount))
{
returnvalue = false;
}
}
if (Convert.ToDecimal(myCheckedCount[i]) > Convert.ToDecimal(CheckedCount1))
{
returnvalue = false;
}
}
}
}
if (model.FromType == "2")
{
for (int i = 0; i < myID.Length; i++)
{
string ProductCount = "00";
string CheckedCount = "00";
string CheckedCount1 = "00";
string pursql = "select ProductedCount,ApplyCheckCount as CheckedCount,isnull(ProductedCount,0)-isnull(ApplyCheckCount,0) as CheckedCount1 from officedba.ManufactureTaskDetail as a where a.ID=@ID";
SqlCommand purcomm = new SqlCommand();
purcomm.Parameters.Add(SqlHelper.GetParameter("@ID", myID[i]));
purcomm.CommandText = pursql;
DataTable dt = SqlHelper.ExecuteSearch(purcomm);
if (dt.Rows.Count > 0)
{
ProductCount = dt.Rows[0]["ProductedCount"].ToString();
CheckedCount = dt.Rows[0]["CheckedCount"].ToString();
CheckedCount1 = dt.Rows[0]["CheckedCount1"].ToString();
if (ProductCount != "" && CheckedCount != "")
{
if (Convert.ToDecimal(ProductCount) == Convert.ToDecimal(CheckedCount))
{
returnvalue = false;
}
}
if (Convert.ToDecimal(myCheckedCount[i]) > Convert.ToDecimal(CheckedCount1))
{
returnvalue = false;
}
}
}
}
return returnvalue;
}
public static bool CloseBill(StorageQualityCheckApplay model, string method)
{
ArrayList listsql = new ArrayList();
SqlCommand comm = new SqlCommand();
StringBuilder sql = new StringBuilder();
sql.AppendLine(" UPDATE officedba.QualityCheckApplay SET");
sql.AppendLine(" BillStatus = @BillStatus,");
sql.AppendLine(" ModifiedUserID = @ModifiedUserID,");
if (method == "0")
{
sql.AppendLine(" Closer= @Closer,");
sql.AppendLine(" CloseDate = @CloseDate, ");
}
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.MdodifiedDate));
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("@CloseDate", model.CloseDate));
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 bool ConfirmBill(StorageQualityCheckApplay model)
{
return StorageQualityCheckProDBHelper.ConfirmBill(model);
}
public static bool GetCheckSaveCount(StorageQualityCheckApplay model, string IDList, string TheCheckedCount)
{
return StorageQualityCheckProDBHelper.GetCheckSaveCount(model, IDList, TheCheckedCount);
}
/// <summary>
/// 取消确认时回写数据
/// </summary>
/// <param name="CheckedCount"></param>
/// <param name="ID"></param>
/// <param name="FromType"></param>
/// <returns></returns>
public static bool UpBackkData(string CheckedCount, string ID, string FromType, StorageQualityCheckApplay model)
{
string companyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD;
string ModifiedID = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).UserID;
return StorageQualityCheckProDBHelper.UpPurDetail(CheckedCount, ID, FromType, ModifiedID, companyCD, model);
}
public static bool CloseBill(StorageQualityCheckApplay model, string Method)
{
return StorageQualityCheckProDBHelper.CloseBill(model, Method);
}
public static DataTable GetQualityList(StorageQualityCheckApplay model, DateTime EndCheckDate, string FlowStatus, string EFIndex, string EFDesc, ref int TotalCount)
{
return StorageQualityCheckProDBHelper.GetQualityCheckList(model, EndCheckDate, FlowStatus, EFIndex, EFDesc, ref TotalCount);
}
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnImport_Click(object sender, ImageClickEventArgs e)
{
try
{
string companyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD;
StorageQualityCheckApplay model = new StorageQualityCheckApplay();
model.ApplyNO = this.txtInNo.Value.Trim();
model.Title = this.txtTitle.Value.Trim();
model.Checker = this.HiddenUser.Value.Trim();
model.CustID = int.Parse(this.hiddenCustID.Value);
model.CheckDeptID = this.HiddenDept.Value;
model.CheckDate = Convert.ToDateTime("1800-1-1");
if (this.BeginCheckDate.Value != "")
{
model.CheckDate = Convert.ToDateTime(this.BeginCheckDate.Value);
}
string EndDate = "9999-2-3";
if (this.EndCheckDate.Value != "")
{
EndDate = this.EndCheckDate.Value;
}
model.CompanyCD = companyCD;
model.FromType = this.FromType.Value;
model.CheckType = this.txtCheckType.Value;
model.CheckMode = this.txtCheckMode.Value;
model.BillStatus = this.BillStatus.Value;
string EFIndex = GetBillExAttrControl1.GetExtIndexValue;
string EFDesc = GetBillExAttrControl1.GetExtTxtValue;
string FlowStatus = this.ddlFlowStatus.Value;
model.Attachment = " ID Desc";
if (this.HiddenOrderID.Value != "0")
{
string[] myOrder = this.HiddenOrderID.Value.Split('_');
if (myOrder[1] == "a")
{
model.Attachment = myOrder[0] + " asc ";
}
else
{
model.Attachment = myOrder[0] + " desc ";
}
}
model.Creater = -100;
model.Confirmor = -100;
int TotalCount = 0;
DataTable dt = StorageQualityCheckPro.GetQualityList(model, Convert.ToDateTime(EndDate), FlowStatus, EFIndex, EFDesc, ref TotalCount);
//导出标题
string headerTitle = "单据编号|单据主题|源单类型|往来单位|往来单位大类|检验方式|报检人员|报检部门|报检日期|单据状态|审批状态";
string[] header = headerTitle.Split('|');
//导出标题所对应的列字段名称
string columnFiled = "ApplyNo|Title|FromType|CustName|CustBigType|CheckMode|EmployeeName|DeptName|CheckDate|BillStatus|FlowStatus";
string[] field = columnFiled.Split('|');
XBase.Common.OutputToExecl.ExportToTable(this.Page, dt, header, field, "质检申请列表");
}
catch
{ }
}
protected void LoadPrintInfo()
{
PrintParameterSettingModel pModel = new PrintParameterSettingModel();
pModel.CompanyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD;
pModel.BillTypeFlag = int.Parse(ConstUtil.BILL_TYPEFLAG_QUALITYCHECK);
pModel.PrintTypeFlag = ConstUtil.PRINTBILL_TYPEFLAG_QUALITYADD;
StorageQualityCheckApplay model = new StorageQualityCheckApplay();
model.CompanyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD;
model.ID = this.intID;
/*此处需注意在模板设置表里的字段和取基本信息的字段是否一致*/
string[,] aBase = {
{ "{ExtField1}", "ExtField1"},
{ "{ExtField2}", "ExtField2"},
{ "{ExtField3}", "ExtField3"},
{ "{ExtField4}", "ExtField4"},
{ "{ExtField5}", "ExtField5"},
{ "{ExtField6}", "ExtField6"},
{ "{ExtField7}", "ExtField7"},
{ "{ExtField8}", "ExtField8"},
{ "{ExtField9}", "ExtField9"},
{ "{ExtField10}", "ExtField10"},
{ "单据编号", "ApplyNo"},
{ "主题", "Title"},
{ "源单类型", "FromType" },
{ "往来单位", "CustName" },
{ "往来单位大类", "CustBigTypeName" },
{ "质检类别", "CheckType" },
{ "生产负责人", "PrincipalName" },
{ "生产部门", "DeptName" },
{ "检验方式", "CheckMode" },
{ "报检人员", "CheckerName" },
{ "报检部门", "CheckDeptName" },
{ "报检日期", "CheckDate" },
{ "报检数量合计", "CountTotal" },
{ "单据状态", "BillStatusName"},
{ "制单人", "CreatorName"},
{ "制单日期", "CreateDate"},
{ "确认人", "ConfirmorName"},
{ "确认日期", "ConfirmDate"},
{ "结单人", "CloserName"},
{ "结单日期", "CloseDate"},
{ "最后更新人", "ModifiedUserID"},
{ "最后更新日期", "ModifiedDate"},
{ "备注", "Remark"},
};
string[,] aDetail = {
{ "序号", "SortNo"},
{ "物品名称", "ProductName" },
{ "物品编号", "ProdNo"},
{ "单位", "CodeName" },
{ "报检数量", "ProductCount"},
{ "已报检数量", "CheckedCount"},
{ "已检数量", "RealCheckedCount"},
{ "备注", "Remark"}
};
if (_isMoreUnit)
{// 启用多计量单位
aDetail = new string[,]
{
{ "序号", "SortNo"},
{ "物品名称", "ProductName" },
{ "物品编号", "ProdNo"},
{ "基本单位", "CodeName" },
{ "基本数量", "ProductCount"},
{ "单位", "UsedUnitName" },
{ "报检数量", "UsedUnitCount"},
{ "已报检数量", "CheckedCount"},
{ "已检数量", "RealCheckedCount"},
{ "备注", "Remark"}
};
}
#region 扩展属性
DataTable dtExtTable = TableExtFieldsBus.GetAllList(((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD, "", "officedba.QualityCheckApplay");
int countExt = 0;
for (int i = 0; i < dtExtTable.Rows.Count; i++)
{
aBase[i, 0] = dtExtTable.Rows[i]["EFDesc"].ToString();
countExt++;
}
#endregion
DataTable dbPrint = XBase.Business.Common.PrintParameterSettingBus.GetPrintParameterSettingInfo(pModel);
DataTable dt = StorageQualityCheckPro.GetOneQuality(model.ID, model.CompanyCD);
Convert(dt);
DataTable dtDetail = StorageQualityCheckPro.GetQualityDetail(model.ID, model.CompanyCD);
string strBaseFields = "";
string strDetailFields = "";
if (dbPrint.Rows.Count > 0)
{
isSeted.Value = "1";
strBaseFields = dbPrint.Rows[0]["BaseFields"].ToString();
strDetailFields = dbPrint.Rows[0]["DetailFields"].ToString();
}
else
{
isSeted.Value = "0";
strBaseFields = GetDefaultFields(aBase);
/*基本信息字段+扩展信息字段*/
for (int i = 0; i < countExt; i++)
{
strBaseFields += "|ExtField" + (i + 1);
}
strDetailFields = GetDefaultFields(aDetail);
}
#region 主表信息
if (!string.IsNullOrEmpty(strBaseFields))
{
tableBase.InnerHtml = WritePrintPageTable("质检申请单", strBaseFields, strDetailFields, aBase, aDetail, dt, dtDetail, true);
}
#endregion
#region 明细信息
if (!string.IsNullOrEmpty(strDetailFields))
{
tableDetail.InnerHtml = WritePrintPageTable("质检申请单", strBaseFields, strDetailFields, aBase, aDetail, dt, dtDetail, false);
}
#endregion
}