/// <summary>
/// 领料单插入
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static bool InsertTakeMaterial(TakeMaterialModel 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.TakeNo, 0);
logModel.Element = ConstUtil.LOG_PROCESS_INSERT;
succ = TakeMaterialDBHelper.InsertTakeMaterial(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 GetTakeDetailInfo(TakeMaterialModel model)
{
try
{
return TakeMaterialDBHelper.GetTakeDetailInfo(model);
}
catch (System.Exception ex)
{
throw ex;
}
}
/// <summary>
/// 确认或结单
/// </summary>
/// <param name="model"></param>
/// <param name="isConfirm"></param>
/// <returns></returns>
public static bool ConfirmOrCompleteTakeMaterial(TakeMaterialModel model, int OperateType)
{
string loginUserID = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).UserID;
//string loginUserID = "admin";//[待修改]
return TakeMaterialDBHelper.ConfirmOrCompleteTakeMaterial(model, loginUserID, OperateType);
}
/// <summary>
/// 发料
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static bool SendTakeMaterial(TakeMaterialModel model,out string reason)
{
try
{
return TakeMaterialDBHelper.SendTakeMaterial(model,out reason);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 通过检索条件查询领料单信息
/// </summary>
/// <returns>DataTable</returns>
public static DataTable GetTakeMaterialListBycondition(TakeMaterialModel model, string TakeDateStart, string TakeDateEnd, int BillTypeFlag, int BillTypeCode, int FlowStatus, string EFIndex, string EFDesc, int pageIndex, int pageCount, string OrderBy, ref int totalCount)
{
try
{
return TakeMaterialDBHelper.GetTakeMaterialListBycondition(model, TakeDateStart, TakeDateEnd, BillTypeFlag, BillTypeCode, FlowStatus, EFIndex, EFDesc, pageIndex, pageCount, OrderBy, ref totalCount);
}
catch (System.Exception ex)
{
throw ex;
}
}
/// <summary>
/// 发料(此处可能需要判断当前物品A所在的仓库A1在分仓存量表里是否存在，如果存在，再判断分仓存量表里的库存是否大于当前该物品的领料统计，如果小于返回提示)
/// </summary>
/// <param name="model"></param>
public static bool SendTakeMaterial(TakeMaterialModel model,out string reason)
{
//判断当前物品在分仓存量表里是否存在,如果存在，再判断分仓存量表里的库存是否大于当前该物品的领料数量，如果大于则更新分仓存量表，如果小于则返回提示
//更新分仓库存量表
//更新领料表
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
try
{
ArrayList listADD = new ArrayList();
int tempTotalSucceed = 0;
bool tempOperateResult = false;
string tempReason = "";
string intTake = "";
DataTable dbTake = GetTakeInfo(model);
if (dbTake.Rows.Count > 0)
{
intTake = dbTake.Rows[0]["ID"].ToString();
if (!string.IsNullOrEmpty(dbTake.Rows[0]["Handout"].ToString()))
{
reason = "已经发过料了";
}
else
{
#region 业务处理
DataTable dtDetail = GetTakeDetailInfo(model);
if (dtDetail.Rows.Count > 0)
{
#region 明细处理
for (int i = 0; i < dtDetail.Rows.Count; i++)
{
decimal TakeCount = decimal.Parse(dtDetail.Rows[i]["TakeCount"].ToString());
int ProductID = int.Parse(dtDetail.Rows[i]["ProductID"].ToString());
int StorageID = int.Parse(dtDetail.Rows[i]["StorageID"].ToString());
string BatchNo = dtDetail.Rows[i]["BatchNo"].ToString();
if (string.IsNullOrEmpty(BatchNo))
{
BatchNo = "";
}
string TakeNo =dtDetail.Rows[i]["TakeNo"].ToString();
decimal Price = decimal.Parse(dtDetail.Rows[i]["Price"].ToString());
decimal productCount = InStorageProductCount(model.CompanyCD, ProductID, StorageID,BatchNo);/*现有存量*/
#region 1.判断当前物品的领料数量是否大于分仓存量表中的库存量
if (TakeCount <= productCount)
{
#region 更新分仓存量表
StringBuilder sqlUpdate = new StringBuilder();
SqlCommand comUpdate = new SqlCommand();
sqlUpdate.AppendLine("update officedba.StorageProduct set ProductCount=isnull(ProductCount,0)-@TakeCount where StorageID=@StorageID and ProductID=@ProductID and CompanyCD=@CompanyCD and isnull(BatchNo,'')=@BatchNo");
comUpdate.CommandText = sqlUpdate.ToString();
comUpdate.Parameters.Add(SqlHelper.GetParameter("@BatchNo", BatchNo));
comUpdate.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
comUpdate.Parameters.Add(SqlHelper.GetParameter("@TakeCount", TakeCount));
comUpdate.Parameters.Add(SqlHelper.GetParameter("@StorageID", StorageID));
comUpdate.Parameters.Add(SqlHelper.GetParameter("@ProductID", ProductID));
listADD.Add(comUpdate);
#endregion
tempTotalSucceed++;
}
else
{
if (productCount == decimal.Parse("-1"))
{
tempReason = tempReason + "第" + (i + 1) + "行中的物品在当前所选的仓库中不存在<br>";
}
else
{
tempReason = tempReason + "第" + (i + 1) + "行中的领料数量大于该物品的库存量" + Decimal.Round(productCount, 2).ToString() + "<br>";
}
}
#endregion
#region 2.写入数据到库存流水账表
StorageAccountModel modelSA = new StorageAccountModel();
modelSA.CompanyCD = model.CompanyCD;
modelSA.BillType = ConstUtil.STORAGEACCOUNT_BILLTYPE_TAKE; /*单据类型*/
modelSA.ProductID = ProductID; /*物品ID*/
modelSA.StorageID = StorageID; /*仓库ID*/
modelSA.BatchNo = BatchNo; /*批次*/
modelSA.BillNo = TakeNo; /*单据编号*/
modelSA.HappenCount = TakeCount; /*操作数量*/
modelSA.Creator = userInfo.EmployeeID; /*操作人*/
modelSA.Price = Price; /*单价*/
modelSA.ProductCount = productCount-TakeCount; /*现有存量*/
modelSA.PageUrl = "Pages/Office/ProductionManager/TakeMaterial_Add.aspx?ModuleID=" + ConstUtil.MODULE_ID_TAKEMATERIAL_EDIT + "&intTakeID=" + intTake;/*页面URL*/
listADD.Add(StorageAccountDBHelper.InsertStorageAccountBySqlCommand(modelSA));
#endregion
}
#endregion
if (dtDetail.Rows.Count > 0 && dtDetail.Rows.Count == tempTotalSucceed)
{
#region 更新领料表中的发料人和发料时间
if (!string.IsNullOrEmpty(model.TakeDate.ToShortDateString()) && !string.IsNullOrEmpty(model.Handout.ToString()))
{
//例：Update officedba.TakeMaterial set Handout=@Handout,TakeDate=@TakeDate where CompanyCD=@CompanyCD and ID=@ID
StringBuilder sqlTake = new StringBuilder();
sqlTake.AppendLine("Update officedba.TakeMaterial set Handout=@Handout,TakeDate=@TakeDate where CompanyCD=@CompanyCD and ID=@ID");
SqlCommand comTake = new SqlCommand();
comTake.CommandText = sqlTake.ToString();
comTake.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
comTake.Parameters.Add(SqlHelper.GetParameter("@ID", model.ID));
comTake.Parameters.Add(SqlHelper.GetParameter("@Handout", model.Handout));
comTake.Parameters.Add(SqlHelper.GetParameter("@TakeDate", model.TakeDate));
listADD.Add(comTake);
if (SqlHelper.ExecuteTransWithArrayList(listADD))
{
tempOperateResult = true;
}
}
#endregion
}
reason = tempReason;
}
else
{
reason = "领料单明细中没有对应的物品需要发料";
}
#endregion
}
}
else
{
reason = "没有找到相关记录";
}
return tempOperateResult;
}
catch (Exception ex) { throw ex; }
}
/// <summary>
/// 通过检索条件查询领料单信息
/// </summary>
/// <param name="model"></param>
/// <param name="BillTypeFlag"></param>
/// <param name="BillTypeCode"></param>
/// <returns></returns>
public static DataTable GetTakeMaterialListBycondition(TakeMaterialModel model, string TakeDateStart, string TakeDateEnd, 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 a.CompanyCD,a.ID,a.TakeNo,a.Subject,a.FromType,a.TaskID,isnull( f.TaskNo,'')as TaskNo,a.ModifiedDate,");
searchSql.AppendLine(" a.ProcessDeptID,isnull(b.DeptName,'')as ProcessDeptName,a.ManufactureType,");
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.FromType=0 then '无来源' when a.FromType=1 then '生产任务单' end as strFromType,");
searchSql.AppendLine(" case when a.ManufactureType=0 then '普通' when a.ManufactureType=1 then '返修' when a.ManufactureType=2 then '拆件' end as strManufactureType,");
searchSql.AppendLine(" isnull(a.Taker,'')as Taker,isnull(c.EmployeeName,'')as TakerReal,");
searchSql.AppendLine(" isnull(a.Handout,'')as Handout,isnull(d.EmployeeName,'')as HandoutReal,");
searchSql.AppendLine(" isnull( CONVERT(CHAR(10), a.TakeDate, 23),'') as TakeDate,a.BillStatus,p.ProjectName,a.ProjectID,");
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 e.FlowStatus=1 then '待审批' when e.FlowStatus=2 then '审批中' when e.FlowStatus=3 then '审批通过' when e.FlowStatus=4 then '审批不通过' when e.FlowStatus=5 then '撤消审批' end as strFlowStatus,");
searchSql.AppendLine(" isnull(e.FlowStatus,'0')as FlowStatus");
searchSql.AppendLine("from officedba.TakeMaterial a");
searchSql.AppendLine("left join officedba.Deptinfo b on a.ProcessDeptID=b.ID");
searchSql.AppendLine("left join officedba.EmployeeInfo c on a.Taker=c.ID");
searchSql.AppendLine("left join officedba.EmployeeInfo d on a.Handout=d.ID");
searchSql.AppendLine("LEFT JOIN officedba.ProjectInfo p on a.ProjectID=p.ID ");
searchSql.AppendLine("LEFT JOIN officedba.FlowInstance e ON a.ID=e.BillID and e.BillTypeFlag=@BillTypeFlag and e.BillTypeCode=@BillTypeCode");
searchSql.AppendLine(" and e.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("LEFT JOIN officedba.ManufactureTask f on a.TaskID=f.ID");
searchSql.AppendLine("where a.CompanyCD=@CompanyCD ");
#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.TakeNo))
{
searchSql.AppendLine("and a.TakeNo like @TakeNo");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@TakeNo", "%" + model.TakeNo + "%"));
}
//单据主题
if (!string.IsNullOrEmpty(model.Subject))
{
searchSql.AppendLine(" and a.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 a.FromType=@FromType ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@FromType", model.FromType));
}
}
//生产任务单
if (model.TaskID > 0)
{
searchSql.AppendLine(" and a.TaskID=@TaskID ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@TaskID", model.TaskID.ToString()));
}
//生产部门
if (model.ProcessDeptID > 0)
{
searchSql.AppendLine(" and a.ProcessDeptID=@ProcessDeptID ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProcessDeptID", model.ProcessDeptID.ToString()));
}
//加工类型
if (model.ManufactureType > 0)
{
searchSql.AppendLine(" and a.ManufactureType=@ManufactureType ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ManufactureType", model.ManufactureType.ToString()));
}
//领料人
if (model.Taker > 0)
{
searchSql.AppendLine(" and a.Taker=@Taker ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Taker", model.Taker.ToString()));
}
//领料单控件（取出发过料的所有领料单）
if (model.Handout == -1)
{
searchSql.AppendLine(" and a.Handout is not null ");
}
//发料人
if (model.Handout > 0)
{
searchSql.AppendLine(" and a.Handout=@Handout ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Handout", model.Handout.ToString()));
}
//发料起始日期
if (!string.IsNullOrEmpty(TakeDateStart))
{
searchSql.AppendLine(" and a.TakeDate>=@TakeDateStart ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@TakeDateStart", TakeDateStart));
}
//发料截止日期
if (!string.IsNullOrEmpty(TakeDateEnd))
{
searchSql.AppendLine(" and a.TakeDate<=@TakeDateEnd ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@TakeDateEnd", TakeDateEnd));
}
//单据状态
if (!string.IsNullOrEmpty(model.BillStatus))
{
if (int.Parse(model.BillStatus) > 0)
{
searchSql.AppendLine("and a.BillStatus=@BillStatus ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@BillStatus", model.BillStatus));
}
}
if (model.ProjectID > 0)
{
searchSql.AppendLine(" and a.ProjectID=@ProjectID ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProjectID", model.ProjectID.ToString()));
}
//审批状态
if (FlowStatus > -1)
{
searchSql.AppendLine("and e.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 a.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="CompanyCD"></param>
/// <param name="BillTypeFlag"></param>
/// <param name="BillTypeCode"></param>
/// <param name="BillID"></param>
/// <param name="loginUserID"></param>
/// <returns></returns>
public static bool CancelConfirmOperate(TakeMaterialModel 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.TakeMaterial 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>
/// <returns></returns>
public static DataTable GetTakeInfo(TakeMaterialModel model)
{
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
#region 查询语句
//查询SQL拼写
StringBuilder infoSql = new StringBuilder();
infoSql.AppendLine("select * from (");
infoSql.AppendLine(" select a.ID,a.CompanyCD,a.TakeNo,a.Subject,a.FromType,a.TaskID,b.TaskNo,");
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(" 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.SaleDeptID,d.DeptName,Convert(numeric(22,"+userInfo.SelPoint+"),a.CountTotal) as CountTotal,a.Creator,e.EmployeeName as CreatorReal,");
infoSql.AppendLine(" isnull( CONVERT(CHAR(10), a.CreateDate, 23),'') as CreateDate,a.Taker,f.EmployeeName as TakerReal,");
infoSql.AppendLine(" a.Handout,g.EmployeeName as HandoutReal,isnull( CONVERT(CHAR(10), a.TakeDate, 23),'') as TakeDate,");
infoSql.AppendLine(" a.BillStatus,a.Confirmor,h.EmployeeName as ConfirmorReal,");
infoSql.AppendLine(" isnull( CONVERT(CHAR(10), a.Confirmdate, 23),'') as ConfirmDate,");
infoSql.AppendLine(" a.Closer,i.EmployeeName as CloserReal,");
infoSql.AppendLine(" isnull( CONVERT(CHAR(10), a.CloseDate, 23),'') as CloseDate,a.Remark,a.ProjectID,p.ProjectName,");
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.TakeMaterial a");
infoSql.AppendLine(" left join officedba.ManufactureTask b on a.TaskID=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.Creator=e.ID");
infoSql.AppendLine(" left join officedba.EmployeeInfo f on a.Taker =f.ID");
infoSql.AppendLine(" left join officedba.EmployeeInfo g on a.Handout=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(" left join officedba.ProjectInfo p on a.ProjectID=p.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="reason"></param>
/// <returns></returns>
public static bool isCanTakeMaterial(TakeMaterialModel model, out string reason)
{
string tempReason = "";
bool isCan = true;
#region 领料单明细QL语句
if (model.DetProductID.Length > 0)
{
string[] detProductID = model.DetProductID.Split(',');
string[] detStorageID = model.DetStorageID.Split(',');
string[] detTakeCount = model.DetTakeCount.Split(',');
string[] detBatchNo = model.DetBatchNo.Split(',');
for (int i = 0; i < detProductID.Length; i++)
{
decimal TakeCount = decimal.Parse(detTakeCount[i].ToString());
int ProductID = int.Parse(detProductID[i].ToString());
int StorageID = int.Parse(detStorageID[i].ToString());
string BatchNo = detBatchNo[i].ToString();
decimal productCount = InStorageProductCount(model.CompanyCD, ProductID, StorageID, BatchNo);
if (TakeCount > productCount)
{
isCan = false;
if (productCount == decimal.Parse("-1"))
{
tempReason = tempReason + "第" + (i + 1) + "行中的物品在当前所选的仓库中不存在<br>";
}
else
{
tempReason = tempReason + "第" + (i + 1) + "行中的领料数量大于该物品的库存量" + Decimal.Round(productCount, 2).ToString() + "<br>";
}
}
}
}
#endregion
reason = tempReason;
return isCan;
}
/// <summary>
/// 领料单插入
/// </summary>
/// <param name="model"></param>
/// <param name="loginUserID"></param>
/// <param name="ID"></param>
/// <returns></returns>
public static bool InsertTakeMaterial(TakeMaterialModel model, Hashtable htExtAttr, string loginUserID, out string ID)
{
ArrayList listADD = new ArrayList();
bool result = false;
//#region 传参
try
{
#region 领料单添加SQL语句
StringBuilder sqlTake = new StringBuilder();
sqlTake.AppendLine("INSERT INTO officedba.TakeMaterial ");
sqlTake.AppendLine(" (CompanyCD ");
sqlTake.AppendLine(" ,TakeNo ");
sqlTake.AppendLine(" ,Subject ");
sqlTake.AppendLine(" ,FromType ");
sqlTake.AppendLine(" ,TaskID ");
sqlTake.AppendLine(" ,ProcessDeptID ");
sqlTake.AppendLine(" ,ManufactureType ");
sqlTake.AppendLine(" ,SaleID ");
sqlTake.AppendLine(" ,SaleDeptID ");
sqlTake.AppendLine(" ,CountTotal ");
sqlTake.AppendLine(" ,Creator ");
sqlTake.AppendLine(" ,CreateDate ");
sqlTake.AppendLine(" ,Taker ");
sqlTake.AppendLine(" ,BillStatus ");
sqlTake.AppendLine(" ,Remark ");
sqlTake.AppendLine(" ,ProjectID ");
sqlTake.AppendLine(" ,ModifiedDate ");
sqlTake.AppendLine(" ,ModifiedUserID) ");
sqlTake.AppendLine(" VALUES ");
sqlTake.AppendLine(" (@CompanyCD ");
sqlTake.AppendLine(" ,@TakeNo ");
sqlTake.AppendLine(" ,@Subject ");
sqlTake.AppendLine(" ,@FromType ");
sqlTake.AppendLine(" ,@TaskID ");
sqlTake.AppendLine(" ,@ProcessDeptID ");
sqlTake.AppendLine(" ,@ManufactureType ");
sqlTake.AppendLine(" ,@SaleID ");
sqlTake.AppendLine(" ,@SaleDeptID ");
sqlTake.AppendLine(" ,@CountTotal ");
sqlTake.AppendLine(" ,@Creator ");
sqlTake.AppendLine(" ,@CreateDate ");
sqlTake.AppendLine(" ,@Taker ");
sqlTake.AppendLine(" ,@BillStatus ");
sqlTake.AppendLine(" ,@Remark ");
sqlTake.AppendLine(" ,@ProjectID ");
sqlTake.AppendLine(" ,getdate() ");
sqlTake.AppendLine(" ,'" + loginUserID + "') ");
sqlTake.AppendLine("set @ID=@@IDENTITY");
SqlCommand comm = new SqlCommand();
comm.CommandText = sqlTake.ToString();
comm.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
comm.Parameters.Add(SqlHelper.GetParameter("@TakeNo", model.TakeNo));
comm.Parameters.Add(SqlHelper.GetParameter("@Subject", model.Subject));
comm.Parameters.Add(SqlHelper.GetParameter("@FromType", model.FromType));
comm.Parameters.Add(SqlHelper.GetParameter("@TaskID", model.TaskID.ToString()));
comm.Parameters.Add(SqlHelper.GetParameter("@ProcessDeptID", model.ProcessDeptID.ToString()));
comm.Parameters.Add(SqlHelper.GetParameter("@ManufactureType", model.ManufactureType));
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("@Creator", model.Creator));
comm.Parameters.Add(SqlHelper.GetParameter("@CreateDate", model.CreateDate));
comm.Parameters.Add(SqlHelper.GetParameter("@Taker", model.Taker.ToString()));
comm.Parameters.Add(SqlHelper.GetParameter("@BillStatus", model.BillStatus));
comm.Parameters.Add(SqlHelper.GetParameter("@Remark", model.Remark));
comm.Parameters.Add(SqlHelper.GetParameter("@ProjectID", model.ProjectID));
comm.Parameters.Add(SqlHelper.GetOutputParameter("@ID", SqlDbType.Int));
listADD.Add(comm);
#endregion
// 拓展属性
SqlCommand cmd = new SqlCommand();
GetExtAttrCmd(model, htExtAttr, cmd);
if (htExtAttr.Count > 0)
listADD.Add(cmd);
//领料单明细添加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="modelProduct"></param>
/// <param name="modelStaff"></param>
/// <param name="modelMachine"></param>
/// <param name="modelMeterial"></param>
/// <param name="loginUserID"></param>
/// <param name="ID"></param>
/// <returns></returns>
public static bool UpdateTakeMaterialInfo(TakeMaterialModel 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.TakeMaterial ");
sqlEdit.AppendLine(" SET CompanyCD = @CompanyCD ");
sqlEdit.AppendLine(" ,Subject = @Subject ");
sqlEdit.AppendLine(" ,FromType = @FromType ");
sqlEdit.AppendLine(" ,TaskID = @TaskID ");
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 ");
sqlEdit.AppendLine(" ,ProjectID = @ProjectID ");
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));
comm.Parameters.Add(SqlHelper.GetParameter("@TaskID", model.TaskID));
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));
comm.Parameters.Add(SqlHelper.GetParameter("@ProjectID", model.ProjectID));
listADD.Add(comm);
#endregion
#region 拓展属性
SqlCommand cmd = new SqlCommand();
GetExtAttrCmd(model, htExtAttr, cmd);
if (htExtAttr.Count > 0)
listADD.Add(cmd);
#endregion
#region 领料单明细处理
// 删除领料单明细
StringBuilder sqlProductDel = new StringBuilder();
sqlProductDel.AppendLine("delete from officedba.TakeMaterialDetail ");
sqlProductDel.AppendLine("where CompanyCD=@CompanyCD");
sqlProductDel.AppendLine("and TakeNo=(");
sqlProductDel.AppendLine(" select top 1 TakeNo from officedba.TakeMaterial 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);
//领料单明细添加SQL语句
SaveDetail(model, listADD, loginUserID);
#endregion
return SqlHelper.ExecuteTransWithArrayList(listADD);
}
/// <summary>
/// 保存明细信息
/// </summary>
/// <param name="model"></param>
/// <param name="list"></param>
private static void SaveDetail(TakeMaterialModel 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[] detTakeCount = model.DetTakeCount.Split(',');
string[] detPrice = model.DetPrice.Split(',');
string[] detTotalPrice = model.DetTotalPrice.Split(',');
string[] detRemark = model.DetRemark.Split(',');
string[] detFromType = model.DetFromType.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.TakeMaterialDetail");
sqlDet.Append(" (CompanyCD,TakeNo,SortNo,ModifiedDate,ModifiedUserID,Remark");
sqlValues.AppendFormat(" VALUES(@CompanyCD,@TakeNo,@SortNo,getdate(),'{0}',@Remark", loginUserID);
commDet.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
commDet.Parameters.Add(SqlHelper.GetParameter("@TakeNo", model.TakeNo));
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 (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(detTakeCount[i].Trim()))
{//基本数量
sqlDet.Append(",TakeCount");
sqlValues.Append(",@TakeCount");
commDet.Parameters.Add(SqlHelper.GetParameter("@TakeCount", detTakeCount[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(detTakeCount[i].Trim()))
{//数量
sqlDet.Append(",TakeCount");
sqlValues.Append(",@TakeCount");
commDet.Parameters.Add(SqlHelper.GetParameter("@TakeCount", detTakeCount[i]));
}
}
sqlDet.Append(")");
sqlValues.Append(")");
commDet.CommandText = sqlDet.ToString() + sqlValues.ToString();
list.Add(commDet);
}
}
/// <summary>
/// 扩展属性保存操作
/// </summary>
/// <returns></returns>
private static void GetExtAttrCmd(TakeMaterialModel model, Hashtable htExtAttr, SqlCommand cmd)
{
try
{
string strSql = string.Empty;
strSql = "UPDATE officedba.TakeMaterial 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 TakeNo = @TakeNo";
cmd.Parameters.AddWithValue("@CompanyCD", model.CompanyCD);
cmd.Parameters.AddWithValue("@TakeNo", model.TakeNo);
cmd.CommandText = strSql;
}
catch (Exception)
{ }
}
/// <summary>
/// 取消确认
/// </summary>
/// <param name="model"></param>
/// <param name="isConfirm"></param>
/// <returns></returns>
public static bool CancelConfirmTakeMaterial(TakeMaterialModel model, int BillTypeFlag, int BillTypeCode)
{
string loginUserID = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).UserID;
return TakeMaterialDBHelper.CancelConfirmOperate(model, BillTypeFlag, BillTypeCode, loginUserID);
}
/// <summary>
/// 领料单明细详细信息
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static DataTable GetTakeDetailInfo(TakeMaterialModel 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.TakeNo,a.SortNo,a.ProductID,b.ProductName,b.ProdNo,a.BatchNo,isnull(b.IsBatchNo,0) as IsBatchNo,");
detSql.AppendLine(" b.UnitID,c.CodeName as UnitName,a.StorageID,d.StorageName,d.StorageNo,Convert(numeric(14,"+userInfo.SelPoint+"),a.TakeCount) as TakeCount,");
detSql.AppendLine(" Convert(numeric(14," + userInfo.SelPoint + "),a.Price) as Price,Convert(numeric(14," + userInfo.SelPoint + "),a.UsedPrice) as UsedPrice,Convert(numeric(22," + userInfo.SelPoint + "),a.TotalPrice) as TotalPrice,");
detSql.AppendLine(" isnull(Convert(numeric(14,"+userInfo.SelPoint+"),BackCount),0) as BackCount,a.Remark,a.FromType,");
detSql.AppendLine(" case when a.FromType=0 then '无来源' when a.FromType=1 then '生产任务单' end as strFromType,a.UsedUnitID,e.CodeName as UsedUnitName,Convert(numeric(14," + userInfo.SelPoint + "),a.UsedUnitCount) as UsedUnitCount ");
detSql.AppendLine(" from officedba.TakeMaterialDetail 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 e on a.UsedUnitID=e.ID");
detSql.AppendLine(" left join officedba.StorageInfo d on a.StorageID=d.ID");
detSql.AppendLine(" where TakeNo=(select top 1 TakeNo from officedba.TakeMaterial where ID=@ID)");
detSql.AppendLine(") as info");
detSql.AppendLine("where CompanyCD=@CompanyCD ");
#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>
/// <param name="isConfirm"></param>
/// <returns></returns>
public static bool ConfirmOrCompleteTakeMaterial(TakeMaterialModel model, string loginUserID, int OperateType)
{
ArrayList listADD = new ArrayList();
if (OperateType == 1)
{
StringBuilder sqlTake = new StringBuilder();
sqlTake.AppendLine(" UPDATE officedba.TakeMaterial SET");
sqlTake.AppendLine(" Confirmor = @Confirmor,");
sqlTake.AppendLine(" ConfirmDate = @ConfirmDate,");
sqlTake.AppendLine(" ModifiedDate = getdate(),");
sqlTake.AppendLine(" BillStatus = 2,");
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("@Confirmor", model.Confirmor));
commTake.Parameters.Add(SqlHelper.GetParameter("@ConfirmDate", model.ConfirmDate));
commTake.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
listADD.Add(commTake);
}
else if (OperateType == 2)
{
StringBuilder sqlTake = new StringBuilder();
sqlTake.AppendLine(" UPDATE officedba.TakeMaterial SET");
sqlTake.AppendLine(" Closer = @Closer,");
sqlTake.AppendLine(" CloseDate = @CloseDate,");
sqlTake.AppendLine(" BillStatus = 4,");
sqlTake.AppendLine(" ModifiedDate = getdate(),");
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("@Closer", model.Closer));
commTake.Parameters.Add(SqlHelper.GetParameter("@CloseDate", model.CloseDate));
commTake.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
listADD.Add(commTake);
}
else
{
StringBuilder sqlTake = new StringBuilder();
sqlTake.AppendLine(" update officedba.TakeMaterial set Closer=null,CloseDate=null,ModifiedDate=getdate(),BillStatus=2,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);
}
return SqlHelper.ExecuteTransWithArrayList(listADD);
}