public static Boolean CheckConsumeNoExists(int ConsumeID, string ConsumeNo)
{
string sSqlText = "Select 1 From Consume_Info Where ConsumeType=" + ConsumeType_ConsumeValue.ToString()
+ " And ConsumeNo='" + ConsumeNo + "' And ID<>" + ConsumeID.ToString();
return(SysGlobal.GetExecSqlIsExist(sSqlText));
}
public static SqlDataReader GetConsumeLstByReader(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_ConsumeValue.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 UpUserInspection(int _ID, string[] FieldValues, string Sql)
{
string sSqlText = "";
if (_ID > 0)
{
sSqlText = sSqlText + " UPDATE InterPhoneCarry_Info SET ConsumeNo='" + FieldValues.GetValue(1) + "'"
+ ",ConsumeUserID=" + FieldValues.GetValue(2) + ""
+ ",Description='" + FieldValues.GetValue(3) + "'"
+ ",ServiceID='" + FieldValues.GetValue(5) + "'"
+ ",ApprovalStatus='" + FieldValues.GetValue(4) + "'";
sSqlText = sSqlText + " WHERE ID=" + _ID + "" + ";";
sSqlText = sSqlText + Sql + " ;";
}
else
{
sSqlText = sSqlText + " Insert Into InterPhoneCarry_Info (TableRecGuid"
+ ", ConsumeNo"
+ ", ConsumeType"
+ ", ConsumeDate"
+ ", OrganID"
+ ", ConsumeUserID"
+ ", CreateUserID"
+ ", ServiceID"
+ ", Description"
+ ", ApprovalStatus)"
+ " Values('" + FieldValues.GetValue(0) + "'"
+ ",'" + FieldValues.GetValue(1) + "'"
+ "," + ConsumeType_ConsumeValue.ToString()
+ ",GetDate()"
+ "," + SysGlobal.GetCurrentUserOrganID().ToString()
+ "," + FieldValues.GetValue(2) + ""
+ "," + SysGlobal.GetCurrentUserID().ToString() + ""
+ ",'" + FieldValues.GetValue(5) + "'"
+ ",'" + FieldValues.GetValue(3) + "'"
+ "," + FieldValues.GetValue(4) + ")";
sSqlText = sSqlText + Sql + " ;";
}
return(DataCommon.QueryData(sSqlText));
}
//更新添加工具档案信息
public static int UpdateSingleConsume(int _ConsumeID, string[] FieldValues, string DetailsSQL)
{
string sSqlText = "begin";
//先减去旧数据的库存
//sSqlText += " Update ToolStock_Info Set ToolStock_Info.Quantity=ToolStock_Info.Quantity + b.Quantity "
// + ", ToolStock_Info.ConsumeQuantity=ToolStock_Info.ConsumeQuantity - b.Quantity"
// + " From ( ConsumeDetails_Info b where b.consumeID In (Select ID From Consume_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.ConsumeQuantity=ToolStock_Info.ConsumeQuantity - 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 (_ConsumeID > 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=" + _ConsumeID + "" + ";";
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_ConsumeValue.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.ConsumeQuantity=ToolStock_Info.ConsumeQuantity + 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 += " Update ToolStock_Info Set ToolStock_Info.Quantity=ToolStock_Info.Quantity - b.Quantity "
+ ", ToolStock_Info.ConsumeQuantity=ToolStock_Info.ConsumeQuantity + 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=1 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 += " end;";
return(DataCommon.QueryData(sSqlText));
}