public static Boolean CheckToolOutNoExists(int ToolOutID, string ToolOutNo)
{
string sSqlText = "Select 1 From ToolOut_Info Where ToolOutType=" + ToolOutType_ToolOutValue.ToString()
+ " And ToolOutNo='" + ToolOutNo + "' And ID<>" + ToolOutID.ToString();
return(SysGlobal.GetExecSqlIsExist(sSqlText));
}
public static SqlDataReader GetToolOutLstByReader(int _OrganID, string sWhereSQL)
{
string sSQL = "";
sSQL = "Select a.*"
+ ", b.OpName as ToolOutOpName, c.OrganName"
+ ", (Case IsNull(ApprovalStatus,0) when 0 then '草稿' when 1 then '正式' end) As ApprovalStatusName"
+ " from ToolOut_Info a "
+ " left join SysUser_Info b on b.Status=0 And a.ToolOutUserID=b.ID"
+ " left join SysOrgan_Info c on c.Status=0 And a.OrganID=c.ID"
+ " Where a.Status=0 And ToolOutType=" + ToolOutType_ToolOutValue.ToString() + sWhereSQL;
if (_OrganID > 0)
{
sSQL = sSQL + " And a.OrganID=" + _OrganID.ToString();
}
sSQL = sSQL + " Order By a.ToolOutDate Desc";
return(DataCommon.GetDataByReader(sSQL));
}
//更新添加工具档案信息
public static int UpdateSingleToolOut(int _ToolOutID, string[] FieldValues, string DetailsSQL)
{
string sSqlText = "begin";
//先减去旧数据的库存
//sSqlText += " Update ToolStock_Info Set ToolStock_Info.Quantity=ToolStock_Info.Quantity + b.Quantity "
// + ", ToolStock_Info.ToolOutQuantity=ToolStock_Info.ToolOutQuantity - b.Quantity"
// + " From ( ToolOutDetails_Info b where b.ToolOutID In (Select ID From ToolOut_Info Where Status=0 "
// + " And TableRecGuid='" + FieldValues.GetValue(0) + "' and IsNull(ApprovalStatus,0)=1)) b and ToolStock_Info.ToolID=b.ToolID;";
sSqlText += " Update ToolStock_Info Set ToolStock_Info.Quantity=ToolStock_Info.Quantity + b.Quantity "
+ ", ToolStock_Info.ToolOutQuantity=ToolStock_Info.ToolOutQuantity - b.Quantity"
+ " From (Select ToolID, Sum(Quantity) As Quantity From ToolOutDetails_Info a, ToolOut_Info b where a.ToolOutID=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 (_ToolOutID > 0)
{
sSqlText = sSqlText + " UPDATE ToolOut_Info SET ToolOutNo='" + FieldValues.GetValue(1) + "'"
+ ",AcceptOrganID=" + FieldValues.GetValue(2) + ""
+ ",ToolOutUserID=" + FieldValues.GetValue(3) + ""
+ ",Description='" + FieldValues.GetValue(4) + "'"
+ ",ApprovalStatus='" + FieldValues.GetValue(5) + "'";
sSqlText = sSqlText + " WHERE ID=" + _ToolOutID + "" + ";";
sSqlText += " ;";
}
else
{
sSqlText = sSqlText + " Insert Into ToolOut_Info (TableRecGuid"
+ ", ToolOutNo"
+ ", ToolOutType"
+ ", ToolOutDate"
+ ", OrganID"
+ ", AcceptOrganID"
+ ", ToolOutUserID"
+ ", CreateUserID"
+ ", Description"
+ ", ApprovalStatus)"
+ " Values('" + FieldValues.GetValue(0) + "'"
+ ",'" + FieldValues.GetValue(1) + "'"
+ "," + ToolOutType_ToolOutValue.ToString()
+ ",GetDate()"
+ "," + SysGlobal.GetCurrentUserOrganID().ToString()
+ "," + FieldValues.GetValue(2) + ""
+ "," + FieldValues.GetValue(3) + ""
+ "," + SysGlobal.GetCurrentUserID().ToString() + ""
+ ",'" + FieldValues.GetValue(4) + "'"
+ "," + FieldValues.GetValue(5) + ")";
sSqlText = sSqlText + " ;";
}
sSqlText = sSqlText + DetailsSQL;
sSqlText += " Insert Into ToolStock_Info (OrganID, ToolID, Quantity)"
+ " (Select distinct " + SysClass.SysGlobal.GetCurrentUserOrganID().ToString() + ", ToolID, 0 From ToolOutDetails_Info Where Status=0 "
+ " And ToolOutID In (Select ID From ToolOut_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.ToolOutQuantity=ToolStock_Info.ToolOutQuantity + b.Quantity"
// + " From ToolOutDetails_Info b where b.ToolOutID In (Select ID From ToolOut_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 += " Update ToolStock_Info Set ToolStock_Info.Quantity=ToolStock_Info.Quantity - b.Quantity "
+ ", ToolStock_Info.ToolOutQuantity=ToolStock_Info.ToolOutQuantity + b.Quantity"
+ " From (Select ToolID, Sum(Quantity) As Quantity From ToolOutDetails_Info a, ToolOut_Info b where a.ToolOutID=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=8 From "
+ " ToolOutDetails_Info b where b.ToolOutID In (Select ID From ToolOut_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 += " end;";
return(DataCommon.QueryData(sSqlText));
}