public static Boolean CheckBorrowNoExists(int ConsumeID, string ConsumeNo) { string sSqlText = "Select 1 From Consume_Info Where ConsumeType=" + ConsumeType_BorrowValue.ToString() + " And ConsumeNo='" + ConsumeNo + "' And ID<>" + ConsumeID.ToString(); return(SysGlobal.GetExecSqlIsExist(sSqlText)); }
public static SqlDataReader GetBorrowLstByReader(int _OrganID, string sWhereSQL) { string sSQL = ""; sSQL = "Select a.*" + ", b.OpName as ConsumeOpName, c.OrganName" + ", (Case IsNull(ApprovalStatus,0) when 0 then '草稿' when 1 then '正式' end) As ApprovalStatusName" + " from Consume_Info a " + " left join SysUser_Info b on b.Status=0 And a.ConsumeUserID=b.ID" + " left join SysOrgan_Info c on c.Status=0 And a.OrganID=c.ID" + " Where a.Status=0 And ConsumeType=" + ConsumeType_BorrowValue.ToString() + sWhereSQL; if (_OrganID > 0) { sSQL = sSQL + " And a.OrganID=" + _OrganID.ToString(); } sSQL = sSQL + " Order By a.ConsumeDate Desc"; return(DataCommon.GetDataByReader(sSQL)); }
//更新添加工具档案信息 public static int UpdateSingleBorrow(int _BorrowID, string[] FieldValues, string DetailsSQL) { string sSqlText = "begin"; //sSqlText += " update ToolStock_Info Set ToolStock_Info.Quantity=ToolStock_Info.Quantity+b.Quantity " // + ", ToolStock_Info.BorrowQuantity=ToolStock_Info.BorrowQuantity - b.Quantity" // + " From ConsumeDetails_Info b where b.consumeID In (Select ID From Consume_Info Where Status=0 " // + " And IsNull(ApprovalStatus,0)=1 And TableRecGuid='" + FieldValues.GetValue(0) + "') and ToolStock_Info.ToolID=b.ToolID;"; sSqlText += " Update ToolStock_Info Set ToolStock_Info.Quantity=ToolStock_Info.Quantity + b.Quantity " + ", ToolStock_Info.BorrowQuantity=ToolStock_Info.BorrowQuantity - b.Quantity" + " From (Select ToolID, Sum(Quantity) As Quantity From ConsumeDetails_Info a, Consume_Info b where a.consumeID=b.Id " + " And b.TableRecGuid='" + FieldValues.GetValue(0) + "' and b.ApprovalStatus=1 And b.Status=0 Group By ToolID) b " + " Where ToolStock_Info.ToolID=b.ToolID And ToolStock_Info.OrganID=" + SysGlobal.GetCurrentUserOrganID().ToString() + ";"; if (_BorrowID > 0) { sSqlText = sSqlText + " UPDATE Consume_Info SET ConsumeNo='" + FieldValues.GetValue(1) + "'" + ",ConsumeUserID=" + FieldValues.GetValue(2) + "" + ",Description='" + FieldValues.GetValue(3) + "'" + ",ApprovalStatus='" + FieldValues.GetValue(4) + "'"; sSqlText = sSqlText + " WHERE ID=" + _BorrowID + "" + ";"; sSqlText += " ;"; } else { sSqlText = sSqlText + " Insert Into Consume_Info (TableRecGuid" + ", ConsumeNo" + ", ConsumeType" + ", ConsumeDate" + ", OrganID" + ", ConsumeUserID" + ", CreateUserID" + ", Description" + ", ApprovalStatus)" + " Values('" + FieldValues.GetValue(0) + "'" + ",'" + FieldValues.GetValue(1) + "'" + "," + ConsumeType_BorrowValue.ToString() + ",GetDate()" + "," + SysGlobal.GetCurrentUserOrganID().ToString() + "," + FieldValues.GetValue(2) + "" + "," + SysGlobal.GetCurrentUserID().ToString() + "" + ",'" + FieldValues.GetValue(3) + "'" + "," + FieldValues.GetValue(4) + ")"; sSqlText = sSqlText + " ;"; } sSqlText = sSqlText + DetailsSQL; sSqlText += " Insert Into ToolStock_Info (OrganID, ToolID, Quantity)" + " (Select distinct " + SysClass.SysGlobal.GetCurrentUserOrganID().ToString() + ", ToolID, 0 From ConsumeDetails_Info Where Status=0 " + " And ConsumeID In (Select ID From Consume_Info Where Status=0 And IsNull(ApprovalStatus,0)=1 And TableRecGuid='" + FieldValues.GetValue(0) + "')" + " And ToolID not in (Select ToolID From ToolStock_Info Where OrganID=" + SysClass.SysGlobal.GetCurrentUserOrganID().ToString() + "));"; sSqlText += " Update ToolStock_Info Set ToolStock_Info.Quantity=ToolStock_Info.Quantity - b.Quantity " + ", ToolStock_Info.BorrowQuantity=ToolStock_Info.BorrowQuantity + b.Quantity" + " From (Select ToolID, Sum(Quantity) As Quantity From ConsumeDetails_Info a, Consume_Info b where a.consumeID=b.Id " + " And b.TableRecGuid='" + FieldValues.GetValue(0) + "'and b.ApprovalStatus=1 And b.Status=0 Group By ToolID) b " + " Where ToolStock_Info.ToolID=b.ToolID And ToolStock_Info.OrganID=" + SysGlobal.GetCurrentUserOrganID().ToString() + ";"; sSqlText += " Update ToolStockDetail_Info Set ToolStockDetail_Info.ToolStatus=2 From " + " ConsumeDetails_Info b where b.consumeID In (Select ID From Consume_Info Where Status=0 And IsNull(ApprovalStatus,0)=1 And TableRecGuid='" + FieldValues.GetValue(0) + "') and ToolStockDetail_Info.ID=b.ToolDetailID" + " And ToolStockDetail_Info.OrganID=" + SysClass.SysGlobal.GetCurrentUserOrganID() + ";"; //sSqlText += " update ToolStock_Info Set ToolStock_Info.Quantity=ToolStock_Info.Quantity-b.Quantity " // + ", ToolStock_Info.BorrowQuantity=ToolStock_Info.BorrowQuantity + b.Quantity" // + " From ConsumeDetails_Info b where b.consumeID In (Select ID From Consume_Info Where Status=0 And IsNull(ApprovalStatus,0)=1 " // + " And TableRecGuid='" + FieldValues.GetValue(0) + "') and ToolStock_Info.ToolID=b.ToolID" // + " And ToolStock_Info.OrganID=" + SysClass.SysGlobal.GetCurrentUserOrganID() + ";"; sSqlText += " end;"; return(DataCommon.QueryData(sSqlText)); }