/// <summary>
/// 退料单插入
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static bool InsertBackMaterial(BackMaterialModel model, Hashtable ht, out string ID)
{
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
ID = "0";
try
{
bool succ = false;
string loginUserID = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).UserID;
LogInfoModel logModel = InitLogInfo(model.BackNo, 0);
logModel.Element = ConstUtil.LOG_PROCESS_INSERT;
succ = BackMaterialDBHelper.InsertBackMaterial(model,ht, loginUserID, out ID);
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, 0, ex);
return false;
}
}
/// <summary>
/// 退料单明细详细信息
/// </summary>
/// <returns>DataTable</returns>
public static DataTable GetBackDetailInfo(BackMaterialModel model)
{
try
{
return BackMaterialDBHelper.GetBackDetailInfo(model);
}
catch (System.Exception ex)
{
throw ex;
}
}
/// <summary>
/// 取消确认
/// </summary>
/// <param name="CompanyCD"></param>
/// <param name="BillTypeFlag"></param>
/// <param name="BillTypeCode"></param>
/// <param name="BillID"></param>
/// <param name="loginUserID"></param>
/// <returns></returns>
public static bool CancelConfirmOperate(BackMaterialModel model, int BillTypeFlag, int BillTypeCode, string loginUserID)
{
ArrayList listADD = new ArrayList();
//#region 传参
try
{
#region 撤消审批流程
#region 撤消审批处理逻辑描述
//可参见撤消审批的存储过程[FlowApproval_Update],个别的判断去掉
//--1.往流程任务历史记录表（officedba.FlowTaskHistory）插1条处理记录，
//--记录的步骤序号为0（表示返回到流程提交人环节)，审批状态为撤销审批
//Insert into officedba.FlowTaskHistory(CompanyCD,FlowInstanceID,FlowNo,BillTypeID,BillID,StepNo,State,operateUserId,operateDate)
//Values(@CompanyCD,@tempFlowInstanceID,@tempFlowNo,@BillTypeFlag,@BillID,0,2,@ModifiedUserID,getdate())
//--2.更新流程任务处理表（officedba.FlowTaskList）中的流程步骤序号为0（表示返回到流程提交人环节）
//Update officedba.FlowTaskList Set StepNo=0,ModifiedDate=getdate(),ModifiedUserID=@ModifiedUserID
//Where CompanyCD=@CompanyCD and FlowInstanceID=@tempFlowInstanceID
//--3更新流程实例表（officedba.FlowInstance）中的流程状态为“撤销审批”
//Update officedba.FlowInstance Set FlowStatus=5,ModifiedDate=getdate(),ModifiedUserID=@ModifiedUserID
//Where CompanyCD=@CompanyCD
//and FlowNo=@tempFlowNo
//and BillTypeFlag=@BillTypeFlag
//and BillTypeCode=@BillTypeCode
//and BillID=@BillID
#endregion
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", loginUserID));
listADD.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", loginUserID));
listADD.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", loginUserID));
listADD.Add(commIns);
#endregion
}
#endregion
#region 处理自己的业务逻辑
StringBuilder sqlTake = new StringBuilder();
sqlTake.AppendLine(" UPDATE officedba.BackMaterial SET");
sqlTake.AppendLine(" Confirmor = null,");
sqlTake.AppendLine(" ConfirmDate = null,");
sqlTake.AppendLine(" ModifiedDate = getdate(),");
sqlTake.AppendLine(" BillStatus = 1,");
sqlTake.AppendLine(" ModifiedUserID = '" + loginUserID + "'");
sqlTake.AppendLine(" Where CompanyCD=@CompanyCD and ID=@ID");
SqlCommand commTake = new SqlCommand();
commTake.CommandText = sqlTake.ToString();
commTake.Parameters.Add(SqlHelper.GetParameter("@ID", model.ID));
commTake.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
listADD.Add(commTake);
#endregion
return SqlHelper.ExecuteTransWithArrayList(listADD);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 确认或结单
/// </summary>
/// <param name="model"></param>
/// <param name="loginUserID"></param>
/// <param name="isConfirm"></param>
/// <returns></returns>
public static bool ConfirmOrCompleteBackMaterial(BackMaterialModel model, string loginUserID, int OperateType)
{
if (OperateType == 1)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine(" UPDATE officedba.BackMaterial SET");
sql.AppendLine(" Confirmor = @Confirmor,");
sql.AppendLine(" ConfirmDate = @ConfirmDate,");
sql.AppendLine(" ModifiedDate = getdate(),");
sql.AppendLine(" BillStatus = 2,");
sql.AppendLine(" ModifiedUserID = '" + loginUserID + "'");
sql.AppendLine(" Where CompanyCD=@CompanyCD and ID=@ID");
SqlParameter[] param = new SqlParameter[4];
param[0] = SqlHelper.GetParameter("@ID", model.ID);
param[1] = SqlHelper.GetParameter("@Confirmor", model.Confirmor);
param[2] = SqlHelper.GetParameter("@ConfirmDate", model.ConfirmDate);
param[3] = SqlHelper.GetParameter("@CompanyCD", model.CompanyCD);
SqlHelper.ExecuteTransSql(sql.ToString(), param);
return SqlHelper.Result.OprateCount > 0 ? true : false;
}
else if (OperateType == 2)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine(" UPDATE officedba.BackMaterial SET");
sql.AppendLine(" Closer = @Closer,");
sql.AppendLine(" CloseDate = @CloseDate,");
sql.AppendLine(" BillStatus = 4,");
sql.AppendLine(" ModifiedDate = getdate(),");
sql.AppendLine(" ModifiedUserID = '" + loginUserID + "'");
sql.AppendLine(" Where CompanyCD=@CompanyCD and ID=@ID");
SqlParameter[] param = new SqlParameter[4];
param[0] = SqlHelper.GetParameter("@ID", model.ID);
param[1] = SqlHelper.GetParameter("@Closer", model.Closer);
param[2] = SqlHelper.GetParameter("@CloseDate", model.CloseDate);
param[3] = SqlHelper.GetParameter("@CompanyCD", model.CompanyCD);
SqlHelper.ExecuteTransSql(sql.ToString(), param);
return SqlHelper.Result.OprateCount > 0 ? true : false;
}
else
{
StringBuilder sql = new StringBuilder();
sql.AppendLine(" update officedba.BackMaterial set Closer=null,CloseDate=null,ModifiedDate=getdate(),BillStatus=2,ModifiedUserID = '" + loginUserID + "'");
sql.AppendLine(" Where CompanyCD=@CompanyCD and ID=@ID");
SqlParameter[] param = new SqlParameter[2];
param[0] = SqlHelper.GetParameter("@ID", model.ID);
param[1] = SqlHelper.GetParameter("@CompanyCD", model.CompanyCD);
SqlHelper.ExecuteTransSql(sql.ToString(), param);
return SqlHelper.Result.OprateCount > 0 ? true : false;
}
}
/// <summary>
/// 收料
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static bool ReceiveBackMaterial(BackMaterialModel model, out string reason)
{
//1.从退料单中取出明细判断源单是领料单还是无源单
//2.如果是领料单更新领料单中的退料数量,更新库存表，增加库存量
//3.如果是无源单，更新库存表，增加库存量
//4.更新退料单中的收料人和收料日期
ArrayList listADD = new ArrayList();
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
int tempTotalSucceed = 0;
bool tempOperateResult = false;
string tempReason = "";
string intBack = "";
DataTable dbBack = GetBackInfo(model);
if (dbBack.Rows.Count > 0)
{
intBack = dbBack.Rows[0]["ID"].ToString();
if (!string.IsNullOrEmpty(dbBack.Rows[0]["Receiver"].ToString()))
{
reason = "已经收过料了";
}
else
{
#region 更新领料单中的退料数量
DataTable dtBackDetail = GetBackDetailInfo(model);
if (dtBackDetail.Rows.Count > 0)
{
#region 明细处理
for (int i = 0; i < dtBackDetail.Rows.Count; i++)
{
int intFromType = int.Parse(dtBackDetail.Rows[i]["FromType"].ToString()); //源单类型
int ProductID = int.Parse(dtBackDetail.Rows[i]["ProductID"].ToString()); //产品ID
int StorageID = int.Parse(dtBackDetail.Rows[i]["StorageID"].ToString()); //仓库ID
Decimal BackCount = Decimal.Parse(dtBackDetail.Rows[i]["BackCount"].ToString());//退料数量
int FromBillID = int.Parse(dtBackDetail.Rows[i]["FromBillID"].ToString()); //源单ID
string FromBillNo = dtBackDetail.Rows[i]["FromBillNo"].ToString(); //源单编号
string BatchNo = dtBackDetail.Rows[i]["BatchNo"].ToString(); //批次
if (string.IsNullOrEmpty(BatchNo))
{
BatchNo = "";
}
string BackNo = dtBackDetail.Rows[i]["BackNo"].ToString();
decimal Price = decimal.Parse(dtBackDetail.Rows[i]["Price"].ToString());
decimal productCount = GetStorageProductCount(model.CompanyCD, ProductID, StorageID, BatchNo);/*现有存量*/
#region 1.是领料单的，更新领料单中退料数量
if (intFromType == 1)
{
int intInStorageProduct = InStorageProductCount(model.CompanyCD, ProductID, StorageID,BatchNo);
if (intInStorageProduct > 0)
{
//判断当前所选的物品，在选择的对应的仓库和批次中是否存在
StringBuilder sqlUpdate = new StringBuilder();
sqlUpdate.AppendLine("update officedba.TakeMaterialDetail set BackCount=isnull(BackCount,0)+@BackCount where ID=@FromBillID and TakeNo=@FromBillNo and CompanyCD=@CompanyCD");
SqlCommand comUpdate = new SqlCommand();
comUpdate.CommandText = sqlUpdate.ToString();
comUpdate.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
comUpdate.Parameters.Add(SqlHelper.GetParameter("@BackCount", BackCount));
comUpdate.Parameters.Add(SqlHelper.GetParameter("@FromBillID", FromBillID));
comUpdate.Parameters.Add(SqlHelper.GetParameter("@FromBillNo", FromBillNo));
listADD.Add(comUpdate);
tempTotalSucceed++;
}
else
{
tempReason = tempReason + "第" + (i + 1) + "行中的物品在当前所选的仓库中不存在<br>";
}
}
else
{
tempTotalSucceed++;
}
#endregion
#region 2.更新库存
StringBuilder sqlSto = new StringBuilder();
sqlSto.AppendLine("update officedba.StorageProduct set ProductCount =isnull(ProductCount,0)+@BackCount where CompanyCD=@CompanyCD and StorageID=@StorageID and ProductID=@ProductID and isnull(BatchNo,'')=@BatchNo");
SqlCommand comSto = new SqlCommand();
comSto.CommandText = sqlSto.ToString();
comSto.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
comSto.Parameters.Add(SqlHelper.GetParameter("@StorageID", StorageID));
comSto.Parameters.Add(SqlHelper.GetParameter("@ProductID", ProductID));
comSto.Parameters.Add(SqlHelper.GetParameter("@BackCount", BackCount));
comSto.Parameters.Add(SqlHelper.GetParameter("@BatchNo", BatchNo));
listADD.Add(comSto);
#endregion
#region 3.写入数据到库存流水账表
StorageAccountModel modelSA = new StorageAccountModel();
modelSA.CompanyCD = model.CompanyCD;
modelSA.BillType = ConstUtil.STORAGEACCOUNT_BILLTYPE_BACK; /*单据类型*/
modelSA.ProductID = ProductID; /*物品ID*/
modelSA.StorageID = StorageID; /*仓库ID*/
modelSA.BatchNo = BatchNo; /*批次*/
modelSA.BillNo = BackNo; /*单据编号*/
modelSA.HappenCount = BackCount; /*操作数量*/
modelSA.Creator = userInfo.EmployeeID; /*操作人*/
modelSA.Price = Price; /*单价*/
modelSA.ProductCount = productCount + BackCount; /*现有存量 =原有的现有存量 +退料数量*/
modelSA.PageUrl = "Pages/Office/ProductionManager/BackMaterial_Add.aspx?ModuleID=" + ConstUtil.MODULE_ID_BACKMATERIAL_EDIT + "&intBackID=" + intBack;/*页面URL*/
listADD.Add(StorageAccountDBHelper.InsertStorageAccountBySqlCommand(modelSA));
#endregion
}
#endregion
#region 4.更新退料单
if (dtBackDetail.Rows.Count > 0 && dtBackDetail.Rows.Count == tempTotalSucceed)
{
StringBuilder sqlBack = new StringBuilder();
sqlBack.AppendLine("update officedba.BackMaterial set Receiver=@Receiver,ReceiveDate=@ReceiveDate where CompanyCD=@CompanyCD and ID=@ID");
SqlCommand comBack = new SqlCommand();
comBack.CommandText = sqlBack.ToString();
comBack.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
comBack.Parameters.Add(SqlHelper.GetParameter("@Receiver", model.Receiver));
comBack.Parameters.Add(SqlHelper.GetParameter("@ReceiveDate", model.ReceiveDate));
comBack.Parameters.Add(SqlHelper.GetParameter("@ID", model.ID));
listADD.Add(comBack);
if (SqlHelper.ExecuteTransWithArrayList(listADD))
{
tempOperateResult = true;
}
}
#endregion
reason = tempReason;
}
else
{
reason = "退料单明细中没有对应的物品需要收料";
}
#endregion
}
}
else
{
reason = "没有找到相关记录";
}
return tempOperateResult;
}
/// <summary>
/// 通过检索条件查询领料单信息
/// </summary>
/// <param name="model"></param>
/// <param name="BillTypeFlag"></param>
/// <param name="BillTypeCode"></param>
/// <returns></returns>
public static DataTable GetBackMaterialListBycondition(BackMaterialModel model, string ReceiveDateStart, string ReceiveDateEnd, int BillTypeFlag, int BillTypeCode, int FlowStatus, string EFIndex, string EFDesc, int pageIndex, int pageCount, string OrderBy, ref int totalCount)
{
#region 查询语句
//查询SQL拼写
StringBuilder searchSql = new StringBuilder();
searchSql.AppendLine("select * from (");
searchSql.AppendLine(" select a.CompanyCD,a.ID,a.BackNo,a.Subject,isnull( CONVERT(CHAR(10), a.ReceiveDate, 23),'') as ReceiveDate,");
searchSql.AppendLine(" a.FromType,a.TakeID,isnull(b.TakeNo,'')as TakeNo,a.ProcessDeptID,isnull(c.DeptName,'')as ProcessDeptName,");
searchSql.AppendLine(" case when a.FromType=0 then '无来源' when a.FromType=1 then '领料单' end as strFromType,");
searchSql.AppendLine(" a.ManufactureType,Convert(numeric(10,2),a.CountTotal) as CountTotal,a.Taker,");
searchSql.AppendLine(" case when a.ManufactureType=0 then '普通' when a.ManufactureType=1 then '返修' when a.ManufactureType=2 then '拆件' end as strManufactureType,");
searchSql.AppendLine(" isnull(d.EmployeeName,'')as TakerReal,isnull(a.Receiver,'')as Receiver,isnull(e.EmployeeName,'')as ReceiverReal,");
searchSql.AppendLine(" a.BillStatus,isnull(f.FlowStatus,'0')as FlowStatus,a.ModifiedDate,f.ID as InstanceID,");
searchSql.AppendLine(" a.ExtField1,a.ExtField2,a.ExtField3,a.ExtField4,a.ExtField5,a.ExtField6,a.ExtField7,a.ExtField8,a.ExtField9,a.ExtField10,");
searchSql.AppendLine(" case when a.BillStatus=1 then '制单' when a.BillStatus=2 then '执行' when a.BillStatus=4 then '手工结单' when a.BillStatus=5 then '自动结单' end as strBillStatusText,");
searchSql.AppendLine(" case when f.FlowStatus=1 then '待审批' when f.FlowStatus=2 then '审批中' when f.FlowStatus=3 then '审批通过' when f.FlowStatus=4 then '审批不通过' when f.FlowStatus=5 then '撤消审批' end as strFlowStatus ");
searchSql.AppendLine(" from officedba.BackMaterial a");
searchSql.AppendLine(" left join officedba.TakeMaterial b on a.TakeID=b.ID");
searchSql.AppendLine(" left join officedba.DeptInfo c on a.ProcessDeptID=c.ID");
searchSql.AppendLine(" left join officedba.EmployeeInfo d on a.Taker=d.ID");
searchSql.AppendLine(" left join officedba.EmployeeInfo e on a.Receiver=e.ID");
searchSql.AppendLine(" LEFT JOIN officedba.FlowInstance f ON a.ID=f.BillID and f.BillTypeFlag=@BillTypeFlag and f.BillTypeCode=@BillTypeCode");
searchSql.AppendLine(" and f.ID=( ");
searchSql.AppendLine(" select max(ID)");
searchSql.AppendLine(" from officedba.FlowInstance H");
searchSql.AppendLine(" where H.CompanyCD = A.CompanyCD");
searchSql.AppendLine(" and H.BillID = A.ID");
searchSql.AppendLine(" and H.BillTypeFlag =@BillTypeFlag");
searchSql.AppendLine(" and H.BillTypeCode =@BillTypeCode)");
searchSql.AppendLine(" )as info");
searchSql.AppendLine("where CompanyCD=@CompanyCD");
if (FlowStatus > 0)
{
searchSql.AppendLine("and InstanceID in(select max(ID) from officedba.FlowInstance where BillTypeFlag=@BillTypeFlag and BillTypeCode=@BillTypeCode group by BillNo)");
}
#endregion
//定义查询的命令
SqlCommand comm = new SqlCommand();
//添加公司代码参数
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD));
//BillTypeFlag
comm.Parameters.Add(SqlHelper.GetParameterFromString("@BillTypeFlag", BillTypeFlag.ToString()));
//BillTypeCode
comm.Parameters.Add(SqlHelper.GetParameterFromString("@BillTypeCode", BillTypeCode.ToString()));
//单据编号
if (!string.IsNullOrEmpty(model.BackNo))
{
searchSql.AppendLine("and BackNo like @BackNo");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@BackNo", "%" + model.BackNo + "%"));
}
//单据主题
if (!string.IsNullOrEmpty(model.Subject))
{
searchSql.AppendLine(" and Subject like @Subject");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Subject", "%" + model.Subject + "%"));
}
//源单类型
if (!string.IsNullOrEmpty(model.FromType))
{
if (int.Parse(model.FromType) > -1)
{
searchSql.AppendLine(" and FromType=@FromType ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@FromType", model.FromType));
}
}
//生产任务单
if (model.TakeID > 0)
{
searchSql.AppendLine(" and TakeID=@TakeID ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@TakeID", model.TakeID.ToString()));
}
//生产部门
if (model.ProcessDeptID > 0)
{
searchSql.AppendLine(" and ProcessDeptID=@ProcessDeptID ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProcessDeptID", model.ProcessDeptID.ToString()));
}
//领料人
if (model.Taker > 0)
{
searchSql.AppendLine(" and a.Taker=@Taker ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Taker", model.Taker.ToString()));
}
//收料人
if (model.Receiver > 0)
{
searchSql.AppendLine(" and Receiver=@Receiver ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Receiver", model.Receiver.ToString()));
}
//收料起始日期
if (!string.IsNullOrEmpty(ReceiveDateStart))
{
searchSql.AppendLine(" and ReceiveDate>=@ReceiveDateStart ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ReceiveDateStart", ReceiveDateStart));
}
//收料截止日期
if (!string.IsNullOrEmpty(ReceiveDateEnd))
{
searchSql.AppendLine(" and ReceiveDate<=@ReceiveDateEnd ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ReceiveDateEnd", ReceiveDateEnd));
}
//单据状态
if (!string.IsNullOrEmpty(model.BillStatus))
{
if (int.Parse(model.BillStatus) > 0)
{
searchSql.AppendLine("and BillStatus=@BillStatus ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@BillStatus", model.BillStatus));
}
}
//审批状态
if (FlowStatus > -1)
{
searchSql.AppendLine("and FlowStatus=@FlowStatus ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@FlowStatus", FlowStatus.ToString()));
}
if (!string.IsNullOrEmpty(EFIndex) && !string.IsNullOrEmpty(EFDesc))
{
if (int.Parse(EFIndex) > 0)
{
searchSql.AppendLine(" and ExtField" + EFIndex + " LIKE @EFDesc");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EFDesc", "%" + EFDesc + "%"));
}
}
//指定命令的SQL文
comm.CommandText = searchSql.ToString();
//执行查询
return SqlHelper.PagerWithCommand(comm, pageIndex, pageCount, OrderBy, ref totalCount);
}
/// <summary>
/// 退料单明细详细信息
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static DataTable GetBackDetailInfo(BackMaterialModel model)
{
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
#region 查询语句
//查询SQL拼写
StringBuilder detSql = new StringBuilder();
detSql.AppendLine("select * from (");
detSql.AppendLine(" select a.CompanyCD,a.ID as DetailID,a.BackNo,a.SortNo,a.ProductID,a.FromBillID,a.FromBillNo,b.ProdNo,b.ProductName,a.StorageID,e.StorageName,");
detSql.AppendLine(" case when a.FromType=0 then '无来源' when a.FromType=1 then '领料单' end as strFromType,");
detSql.AppendLine(" b.UnitID,c.CodeName as UnitName,a.FromType,Convert(numeric(14,"+userInfo.SelPoint+"),f.TakeCount) as TakeCount,Convert(numeric(14,"+userInfo.SelPoint+"),isnull(f.BackCount,0)) as TakeBackCount,");
detSql.AppendLine(" Convert(numeric(14,"+userInfo.SelPoint+"),a.BackCount) as BackCount,Convert(numeric(14,"+userInfo.SelPoint+"),a.Price) as Price,Convert(numeric(12,"+userInfo.SelPoint+"),a.TotalPrice) as TotalPrice,a.Remark,");
detSql.AppendLine(" a.UsedUnitID,u.CodeName as UsedUnitName,Convert(numeric(14," + userInfo.SelPoint + "),a.UsedUnitCount) as UsedUnitCount,Convert(numeric(14," + userInfo.SelPoint + "),a.UsedPrice) as UsedPrice,a.BatchNo,b.IsBatchNo");
detSql.AppendLine(" from officedba.BackMaterialDetail a");
detSql.AppendLine(" left join officedba.ProductInfo b on a.ProductID=b.ID");
detSql.AppendLine(" left join officedba.CodeUnitType c on b.UnitID=c.ID");
detSql.AppendLine(" left join officedba.CodeUnitType u on a.UsedUnitID=u.ID");
detSql.AppendLine(" left join officedba.TakeMaterialDetail f on a.FromBillNo=f.TakeNo and a.FromBillID=f.ID");
detSql.AppendLine(" left join officedba.StorageInfo e on a.StorageID=e.ID");
detSql.AppendLine(" where a.CompanyCD=@CompanyCD and BackNo=(select top 1 BackNo from officedba.BackMaterial where ID=@ID)");
detSql.AppendLine(") as info");
#endregion
//定义查询的命令
SqlCommand comm = new SqlCommand();
//添加公司代码参数
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ID", model.ID.ToString()));
//指定命令的SQL文
comm.CommandText = detSql.ToString();
//执行查询
return SqlHelper.ExecuteSearch(comm);
}
/// <summary>
/// 修改退料单和各明细信息
/// </summary>
/// <param name="model"></param>
/// <param name="loginUserID"></param>
/// <returns></returns>
public static bool UpdateBackMaterialInfo(BackMaterialModel model, Hashtable htExtAttr, string loginUserID)
{
//获取登陆用户ID
ArrayList listADD = new ArrayList();
if (model.ID <= 0)
{
return false;
}
#region 退料单修改SQL语句
StringBuilder sqlEdit = new StringBuilder();
sqlEdit.AppendLine("UPDATE officedba.BackMaterial ");
sqlEdit.AppendLine(" SET CompanyCD = @CompanyCD ");
sqlEdit.AppendLine(" ,Subject = @Subject ");
sqlEdit.AppendLine(" ,FromType = @FromType ");
sqlEdit.AppendLine(" ,TakeID = @TakeID ");
sqlEdit.AppendLine(" ,ProcessDeptID = @ProcessDeptID ");
sqlEdit.AppendLine(" ,ManufactureType = @ManufactureType ");
sqlEdit.AppendLine(" ,SaleID = @SaleID ");
sqlEdit.AppendLine(" ,SaleDeptID = @SaleDeptID ");
sqlEdit.AppendLine(" ,CountTotal = @CountTotal ");
sqlEdit.AppendLine(" ,Taker = @Taker ");
sqlEdit.AppendLine(" ,Remark = @Remark ");
if (int.Parse(model.BillStatus) == 2)
{
sqlEdit.AppendLine(" ,BillStatus = 3 ");
}
sqlEdit.AppendLine(" ,Confirmor=null ");
sqlEdit.AppendLine(" ,ConfirmDate=null ");
sqlEdit.AppendLine(" ,ModifiedDate = getdate() ");
sqlEdit.AppendLine(" ,ModifiedUserID = '" + loginUserID + "' ");
sqlEdit.AppendLine(" WHERE CompanyCD=@CompanyCD and ID=@ID ");
SqlCommand comm = new SqlCommand();
comm.CommandText = sqlEdit.ToString();
comm.Parameters.Add(SqlHelper.GetParameter("@ID", model.ID));
comm.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
comm.Parameters.Add(SqlHelper.GetParameter("@Subject", model.Subject));
comm.Parameters.Add(SqlHelper.GetParameter("@FromType", model.FromType));
if (model.FromType == "0")
{
comm.Parameters.Add(SqlHelper.GetParameter("@TakeID", "0"));
}
else
{
comm.Parameters.Add(SqlHelper.GetParameter("@TakeID", model.TakeID));
}
comm.Parameters.Add(SqlHelper.GetParameter("@ProcessDeptID", model.ProcessDeptID));
comm.Parameters.Add(SqlHelper.GetParameter("@ManufactureType", model.ManufactureType));
comm.Parameters.Add(SqlHelper.GetParameter("@SaleID", model.SaleID));
comm.Parameters.Add(SqlHelper.GetParameter("@SaleDeptID", model.SaleDeptID));
comm.Parameters.Add(SqlHelper.GetParameter("@CountTotal", model.CountTotal));
comm.Parameters.Add(SqlHelper.GetParameter("@Taker", model.Taker));
comm.Parameters.Add(SqlHelper.GetParameter("@Remark", model.Remark));
listADD.Add(comm);
#endregion
#region 拓展属性
SqlCommand cmd = new SqlCommand();
GetExtAttrCmd(model, htExtAttr, cmd);
if (htExtAttr.Count > 0)
listADD.Add(cmd);
#endregion
#region 退料单明细处理
#region 删除退料单明细
StringBuilder sqlProductDel = new StringBuilder();
sqlProductDel.AppendLine("delete from officedba.BackMaterialDetail ");
sqlProductDel.AppendLine("where CompanyCD=@CompanyCD");
sqlProductDel.AppendLine("and BackNo=(");
sqlProductDel.AppendLine(" select top 1 BackNo from officedba.BackMaterial where CompanyCD=@CompanyCD and ID=@ID");
sqlProductDel.AppendLine(" )");
SqlCommand commProductDel = new SqlCommand();
commProductDel.CommandText = sqlProductDel.ToString();
commProductDel.Parameters.Add(SqlHelper.GetParameter("@ID", model.ID));
commProductDel.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
listADD.Add(commProductDel);
#endregion
// 退料单明细添加SQL语句
SaveDetail(model, listADD, loginUserID);
#endregion
return SqlHelper.ExecuteTransWithArrayList(listADD);
}
/// <summary>
/// 退料单详细信息
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static DataTable GetBackInfo(BackMaterialModel model)
{
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
#region 查询语句
//查询SQL拼写
StringBuilder infoSql = new StringBuilder();
infoSql.AppendLine("Select * From (");
infoSql.AppendLine(" select a.CompanyCD,a.ID,a.BackNo,a.Subject,a.FromType,a.TakeID,b.TakeNo,");
infoSql.AppendLine(" case when a.FromType=0 then '无来源' when a.FromType=1 then '领料单' end as strFromType,");
infoSql.AppendLine(" a.ProcessDeptID,j.DeptName as ProcessDeptName,a.ManufactureType,a.SaleID,c.EmployeeName as SaleReal,");
infoSql.AppendLine(" case when a.ManufactureType=0 then '普通' when a.ManufactureType=1 then '返修' when a.ManufactureType=2 then '拆件' end as strManufactureType,");
infoSql.AppendLine(" a.SaleDeptID,d.DeptName,Convert(numeric(22," + userInfo.SelPoint+ "),a.CountTotal) as CountTotal,a.Taker,e.EmployeeName as TakerReal,");
infoSql.AppendLine(" a.Receiver,f.EmployeeName as ReceiverReal,isnull( CONVERT(CHAR(10), a.ReceiveDate, 23),'') as ReceiveDate,a.Remark,a.Creator,g.EmployeeName as CreatorReal,");
infoSql.AppendLine(" isnull( CONVERT(CHAR(10), a.CreateDate, 23),'') as CreateDate,a.BillStatus,a.Confirmor,h.EmployeeName as ConfirmorReal,isnull( CONVERT(CHAR(10), a.ConfirmDate, 23),'') as ConfirmDate,");
infoSql.AppendLine(" case when a.BillStatus=1 then '制单' when a.BillStatus=2 then '执行' when a.BillStatus=4 then '手工结单' when a.BillStatus=5 then '自动结单' end as strBillStatusText,");
infoSql.AppendLine(" a.Closer,i.EmployeeName as CloserReal,isnull( CONVERT(CHAR(10), a.CloseDate, 23),'') as CloseDate,");
infoSql.AppendLine(" a.ExtField1,a.ExtField2,a.ExtField3,a.ExtField4,a.ExtField5,a.ExtField6,a.ExtField7,a.ExtField8,a.ExtField9,a.ExtField10,");
infoSql.AppendLine(" isnull( CONVERT(CHAR(10), a.ModifiedDate, 23),'') as ModifiedDate,a.ModifiedUserID");
infoSql.AppendLine(" from officedba.BackMaterial a");
infoSql.AppendLine(" left join officedba.TakeMaterial b on a.TakeID=b.ID");
infoSql.AppendLine(" left join officedba.EmployeeInfo c on a.SaleID=c.ID");
infoSql.AppendLine(" left join officedba.DeptInfo d on a.SaleDeptID=d.ID");
infoSql.AppendLine(" left join officedba.EmployeeInfo e on a.Taker=e.ID");
infoSql.AppendLine(" left join officedba.EmployeeInfo f on a.Receiver=f.ID");
infoSql.AppendLine(" left join officedba.EmployeeInfo g on a.Creator=g.ID");
infoSql.AppendLine(" left join officedba.EmployeeInfo h on a.Confirmor=h.ID");
infoSql.AppendLine(" left join officedba.EmployeeInfo i on a.Closer=i.ID");
infoSql.AppendLine(" left join officedba.DeptInfo j on a.ProcessDeptID=j.ID");
infoSql.AppendLine(" )as info");
infoSql.AppendLine("Where ID=@ID");
#endregion
//定义查询的命令
SqlCommand comm = new SqlCommand();
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ID", model.ID.ToString()));
//指定命令的SQL文
comm.CommandText = infoSql.ToString();
//执行查询
return SqlHelper.ExecuteSearch(comm);
}
/// <summary>
/// 领料单插入
/// </summary>
/// <param name="model"></param>
/// <param name="loginUserID"></param>
/// <param name="ID"></param>
/// <returns></returns>
public static bool InsertBackMaterial(BackMaterialModel model, Hashtable htExtAttr, string loginUserID, out string ID)
{
ArrayList listADD = new ArrayList();
bool result = false;
//#region 传参
try
{
#region 退料单添加SQL语句
StringBuilder sqlBack = new StringBuilder();
sqlBack.AppendLine("INSERT INTO officedba.BackMaterial ");
sqlBack.AppendLine(" (CompanyCD ");
sqlBack.AppendLine(" ,BackNo ");
sqlBack.AppendLine(" ,Subject ");
sqlBack.AppendLine(" ,FromType ");
sqlBack.AppendLine(" ,TakeID ");
sqlBack.AppendLine(" ,ProcessDeptID ");
sqlBack.AppendLine(" ,ManufactureType ");
sqlBack.AppendLine(" ,SaleID ");
sqlBack.AppendLine(" ,SaleDeptID ");
sqlBack.AppendLine(" ,CountTotal ");
sqlBack.AppendLine(" ,Taker ");
sqlBack.AppendLine(" ,Remark ");
sqlBack.AppendLine(" ,Creator ");
sqlBack.AppendLine(" ,CreateDate ");
sqlBack.AppendLine(" ,BillStatus ");
sqlBack.AppendLine(" ,ModifiedDate ");
sqlBack.AppendLine(" ,ModifiedUserID) ");
sqlBack.AppendLine(" VALUES ");
sqlBack.AppendLine(" (@CompanyCD ");
sqlBack.AppendLine(" ,@BackNo ");
sqlBack.AppendLine(" ,@Subject ");
sqlBack.AppendLine(" ,@FromType ");
sqlBack.AppendLine(" ,@TakeID ");
sqlBack.AppendLine(" ,@ProcessDeptID ");
sqlBack.AppendLine(" ,@ManufactureType ");
sqlBack.AppendLine(" ,@SaleID ");
sqlBack.AppendLine(" ,@SaleDeptID ");
sqlBack.AppendLine(" ,@CountTotal ");
sqlBack.AppendLine(" ,@Taker ");
sqlBack.AppendLine(" ,@Remark ");
sqlBack.AppendLine(" ,@Creator ");
sqlBack.AppendLine(" ,@CreateDate ");
sqlBack.AppendLine(" ,@BillStatus ");
sqlBack.AppendLine(" ,getdate() ");
sqlBack.AppendLine(" ,'"+loginUserID+"') ");
sqlBack.AppendLine("set @ID=@@IDENTITY ");
SqlCommand comm = new SqlCommand();
comm.CommandText = sqlBack.ToString();
comm.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
comm.Parameters.Add(SqlHelper.GetParameter("@BackNo", model.BackNo));
comm.Parameters.Add(SqlHelper.GetParameter("@Subject", model.Subject));
comm.Parameters.Add(SqlHelper.GetParameter("@FromType", model.FromType));
comm.Parameters.Add(SqlHelper.GetParameter("@TakeID", model.TakeID.ToString()));
comm.Parameters.Add(SqlHelper.GetParameter("@ProcessDeptID", model.ProcessDeptID.ToString()));
comm.Parameters.Add(SqlHelper.GetParameter("@ManufactureType", model.ManufactureType.ToString()));
comm.Parameters.Add(SqlHelper.GetParameter("@SaleID", model.SaleID.ToString()));
comm.Parameters.Add(SqlHelper.GetParameter("@SaleDeptID", model.SaleDeptID.ToString()));
comm.Parameters.Add(SqlHelper.GetParameter("@CountTotal", model.CountTotal.ToString()));
comm.Parameters.Add(SqlHelper.GetParameter("@Taker", model.Taker.ToString()));
comm.Parameters.Add(SqlHelper.GetParameter("@Remark", model.Remark));
comm.Parameters.Add(SqlHelper.GetParameter("@Creator", model.Creator));
comm.Parameters.Add(SqlHelper.GetParameter("@CreateDate", model.CreateDate));
comm.Parameters.Add(SqlHelper.GetParameter("@BillStatus", model.BillStatus));
comm.Parameters.Add(SqlHelper.GetOutputParameter("@ID", SqlDbType.Int));
listADD.Add(comm);
#endregion
#region 拓展属性
SqlCommand cmd = new SqlCommand();
GetExtAttrCmd(model, htExtAttr, cmd);
if (htExtAttr.Count > 0)
listADD.Add(cmd);
#endregion
// 退料单明细添加SQL语句
SaveDetail(model, listADD, loginUserID);
if (SqlHelper.ExecuteTransWithArrayList(listADD))
{
ID = comm.Parameters["@ID"].Value.ToString();
result = true;
}
else
{
ID = "0";
}
return result;
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 保存明细信息
/// </summary>
/// <param name="model"></param>
/// <param name="list"></param>
private static void SaveDetail(BackMaterialModel model, ArrayList list, string loginUserID)
{
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
if (model.DetProductID.Length < 1)
{
return;
}
string[] detSortNo = model.DetSortNo.Split(',');
string[] detProductID = model.DetProductID.Split(',');
string[] detStorageID = model.DetStorageID.Split(',');
string[] detBackCount = model.DetBackCount.Split(',');
string[] detPrice = model.DetPrice.Split(',');
string[] detTotalPrice = model.DetTotalPrice.Split(',');
string[] detRemark = model.DetRemark.Split(',');
string[] detFromType = model.DetFromType.Split(',');
string[] detFromBillID = model.DetFromBillID.Split(',');
string[] detFromBillNo = model.DetFromBillNo.Split(',');
string[] detUnitID = model.DetUnitID.Split(',');
string[] detUsedUnitID = model.DetUsedUnitID.Split(',');
string[] detUsedUnitCount = model.DetUsedUnitCount.Split(',');
string[] detExRate = model.DetExRate.Split(',');
string[] detUsedPrice = model.DetUsedPrice.Split(',');
string[] detBatchNo = model.DetBatchNo.Split(',');
for (int i = 0; i < detSortNo.Length; i++)
{
StringBuilder sqlDet = new StringBuilder();
StringBuilder sqlValues = new StringBuilder();
SqlCommand commDet = new SqlCommand();
sqlDet.Append("INSERT INTO officedba.BackMaterialDetail");
sqlDet.Append(" (CompanyCD,BackNo,SortNo,ModifiedDate,ModifiedUserID,Remark");
sqlValues.AppendFormat(" VALUES(@CompanyCD,@BackNo,@SortNo,getdate(),'{0}',@Remark", loginUserID);
commDet.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
commDet.Parameters.Add(SqlHelper.GetParameter("@BackNo", model.BackNo));
commDet.Parameters.Add(SqlHelper.GetParameter("@SortNo", detSortNo[i].ToString()));
commDet.Parameters.Add(SqlHelper.GetParameter("@Remark", detRemark[i].ToString()));
if (!string.IsNullOrEmpty(detProductID[i].Trim()))
{// 产品ID
sqlDet.Append(",ProductID");
sqlValues.Append(",@ProductID");
commDet.Parameters.Add(SqlHelper.GetParameter("@ProductID", detProductID[i]));
}
if (!string.IsNullOrEmpty(detStorageID[i].Trim()))
{// 仓库ID
sqlDet.Append(",StorageID");
sqlValues.Append(",@StorageID");
commDet.Parameters.Add(SqlHelper.GetParameter("@StorageID", detStorageID[i]));
}
if (!string.IsNullOrEmpty(detPrice[i].Trim()))
{//单价
sqlDet.Append(",Price");
sqlValues.Append(",@Price");
commDet.Parameters.Add(SqlHelper.GetParameter("@Price", detPrice[i]));
}
if (!string.IsNullOrEmpty(detTotalPrice[i].Trim()))
{//金额
sqlDet.Append(",TotalPrice");
sqlValues.Append(",@TotalPrice");
commDet.Parameters.Add(SqlHelper.GetParameter("@TotalPrice", detTotalPrice[i]));
}
if (!string.IsNullOrEmpty(detFromType[i].Trim()))
{// 源单类型
sqlDet.Append(",FromType");
sqlValues.Append(",@FromType");
commDet.Parameters.Add(SqlHelper.GetParameter("@FromType", detFromType[i]));
}
if (!string.IsNullOrEmpty(detFromBillID[i].Trim()))
{// 源单编码
sqlDet.Append(",FromBillID");
sqlValues.Append(",@FromBillID");
commDet.Parameters.Add(SqlHelper.GetParameter("@FromBillID", detFromBillID[i]));
}
if (!string.IsNullOrEmpty(detFromBillNo[i].Trim()))
{// 源单编号
sqlDet.Append(",FromBillNo");
sqlValues.Append(",@FromBillNo");
commDet.Parameters.Add(SqlHelper.GetParameter("@FromBillNo", detFromBillNo[i]));
}
if (userInfo.IsMoreUnit)
{
if (!string.IsNullOrEmpty(detUnitID[i].Trim()))
{//基本单位
sqlDet.Append(",UnitID");
sqlValues.Append(",@UnitID");
commDet.Parameters.Add(SqlHelper.GetParameter("@UnitID", detUnitID[i]));
}
if (!string.IsNullOrEmpty(detBackCount[i].Trim()))
{//基本数量
sqlDet.Append(",BackCount");
sqlValues.Append(",@BackCount");
commDet.Parameters.Add(SqlHelper.GetParameter("@BackCount", detBackCount[i]));
}
if (!string.IsNullOrEmpty(detUsedUnitID[i].Trim()))
{//单位
sqlDet.Append(",UsedUnitID");
sqlValues.Append(",@UsedUnitID");
commDet.Parameters.Add(SqlHelper.GetParameter("@UsedUnitID", detUsedUnitID[i]));
}
if (!string.IsNullOrEmpty(detUsedUnitCount[i].Trim()))
{//数量
sqlDet.Append(",UsedUnitCount");
sqlValues.Append(",@UsedUnitCount");
commDet.Parameters.Add(SqlHelper.GetParameter("@UsedUnitCount", detUsedUnitCount[i]));
}
if (!string.IsNullOrEmpty(detExRate[i].Trim()))
{//换算率
sqlDet.Append(",ExRate");
sqlValues.Append(",@ExRate");
commDet.Parameters.Add(SqlHelper.GetParameter("@ExRate", detExRate[i]));
}
if (!string.IsNullOrEmpty(detUsedPrice[i].Trim()))
{//单价
sqlDet.Append(",UsedPrice");
sqlValues.Append(",@UsedPrice");
commDet.Parameters.Add(SqlHelper.GetParameter("@UsedPrice", detUsedPrice[i]));
}
if (!string.IsNullOrEmpty(detBatchNo[i].Trim()))
{//批次
sqlDet.Append(",BatchNo");
sqlValues.Append(",@BatchNo");
commDet.Parameters.Add(SqlHelper.GetParameter("@BatchNo", detBatchNo[i]));
}
}
else
{
if (!string.IsNullOrEmpty(detUnitID[i].Trim()))
{//单位
sqlDet.Append(",UnitID");
sqlValues.Append(",@UnitID");
commDet.Parameters.Add(SqlHelper.GetParameter("@UnitID", detUnitID[i]));
}
if (!string.IsNullOrEmpty(detBackCount[i].Trim()))
{//数量
sqlDet.Append(",BackCount");
sqlValues.Append(",@BackCount");
commDet.Parameters.Add(SqlHelper.GetParameter("@BackCount", detBackCount[i]));
}
}
//if (detUnitID.Length == detProductID.Length && !string.IsNullOrEmpty(detUnitID[i].Split('|')[0].Trim()))
//{// 单位
// sqlDet.Append(",UnitID");
// sqlValues.Append(",@UnitID");
// commDet.Parameters.Add(SqlHelper.GetParameter("@UnitID", detUnitID[i].Split('|')[0]));
//}
//if (detUnitID.Length == detProductID.Length && detUnitID[i].Split('|').Length == 2 && !string.IsNullOrEmpty(detUnitID[i].Split('|')[1].Trim()))
//{// 单位换算率
// sqlDet.Append(",ExRate");
// sqlValues.Append(",@ExRate");
// commDet.Parameters.Add(SqlHelper.GetParameter("@ExRate", detUnitID[i].Split('|')[1]));
//}
//if (detUsedUnitCount.Length == detProductID.Length && !string.IsNullOrEmpty(detUsedUnitCount[i].Trim()))
//{// 基本数量
// sqlDet.Append(",UsedUnitCount");
// sqlValues.Append(",@UsedUnitCount");
// commDet.Parameters.Add(SqlHelper.GetParameter("@UsedUnitCount", detUsedUnitCount[i]));
//}
sqlDet.Append(")");
sqlValues.Append(")");
commDet.CommandText = sqlDet.ToString() + sqlValues.ToString();
list.Add(commDet);
}
}
/// <summary>
/// 扩展属性保存操作
/// </summary>
/// <returns></returns>
private static void GetExtAttrCmd(BackMaterialModel model, Hashtable htExtAttr, SqlCommand cmd)
{
try
{
string strSql = string.Empty;
strSql = "UPDATE officedba.BackMaterial set ";
foreach (DictionaryEntry de in htExtAttr)// fileht为一个Hashtable实例
{
strSql += de.Key.ToString().Trim() + "=@" + de.Key.ToString().Trim() + ",";
cmd.Parameters.AddWithValue("@" + de.Key.ToString().Trim(), de.Value.ToString().Trim());
}
int iLength = strSql.Length - 1;
strSql = strSql.Substring(0, iLength);
strSql += " where CompanyCD = @CompanyCD AND BackNo = @BackNo";
cmd.Parameters.AddWithValue("@CompanyCD", model.CompanyCD);
cmd.Parameters.AddWithValue("@BackNo", model.BackNo);
cmd.CommandText = strSql;
}
catch (Exception)
{ }
}
/// <summary>
/// 取消确认
/// </summary>
/// <param name="model"></param>
/// <param name="isConfirm"></param>
/// <returns></returns>
public static bool CancelConfirmBackMaterial(BackMaterialModel model, int BillTypeFlag, int BillTypeCode)
{
string loginUserID = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).UserID;
return BackMaterialDBHelper.CancelConfirmOperate(model, BillTypeFlag, BillTypeCode, loginUserID);
}
/// <summary>
/// 确认或结单
/// </summary>
/// <param name="model"></param>
/// <param name="isConfirm"></param>
/// <returns></returns>
public static bool ConfirmOrCompleteBackMaterial(BackMaterialModel model, int OperateType)
{
string loginUserID = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).UserID;
//string loginUserID = "admin";//[待修改]
return BackMaterialDBHelper.ConfirmOrCompleteBackMaterial(model, loginUserID, OperateType);
}
/// <summary>
/// 收料
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static bool ReceiveBackMaterial(BackMaterialModel model, out string reason)
{
try
{
return BackMaterialDBHelper.ReceiveBackMaterial(model, out reason);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 通过检索条件查询退料单信息
/// </summary>
/// <returns>DataTable</returns>
public static DataTable GetBackMaterialListBycondition(BackMaterialModel model, string ReceiveDateStart, string ReceiveDateEnd, int BillTypeFlag, int BillTypeCode, int FlowStatus, string EFIndex, string EFDesc, int pageIndex, int pageCount, string OrderBy, ref int totalCount)
{
try
{
return BackMaterialDBHelper.GetBackMaterialListBycondition(model, ReceiveDateStart, ReceiveDateEnd, BillTypeFlag, BillTypeCode, FlowStatus, EFIndex, EFDesc, pageIndex, pageCount, OrderBy, ref totalCount);
}
catch (System.Exception ex)
{
throw ex;
}
}