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)); }