//---新增、修改订单 public static int UpNewOrder(int _ID, string[] FieldValues, string DetailsSQL) { string Sql = ""; if (_ID > 0) { Sql = Sql + " UPDATE InterPhoneOrder_Info SET OrderNo='" + FieldValues.GetValue(1) + "',Description='" + FieldValues.GetValue(3) + "'" + ",Isfinish=" + FieldValues.GetValue(4); Sql = Sql + " WHERE ID=" + _ID + "" + ";"; Sql += " ;"; } else { Sql = Sql + " Insert Into InterPhoneOrder_Info (TableRecGuid" + ", OrderNo" + ", OrderDate" + ", OrganID" + ", OrderUserID" + ", Description" + ", Isfinish)" + " Values('" + FieldValues.GetValue(0) + "'" + ",'" + FieldValues.GetValue(1) + "'" + ",GetDate()" + "," + SysGlobal.GetCurrentUserOrganID().ToString() + ",'" + SysGlobal.GetCurrentUserID().ToString() + "'" + ",'" + FieldValues.GetValue(3) + "'" + "," + FieldValues.GetValue(4) + ")"; Sql = Sql + " ;"; } Sql = Sql + DetailsSQL; return(DataCommon.QueryData(Sql)); }
//更新添加工具档案信息 public static int UpdateSingleSendOut(int _ID, string[] FieldValues, string DetailsSQL) { string sSqlText = "begin"; sSqlText += " Update ToolStock_Info Set ToolStock_Info.Quantity=ToolStock_Info.Quantity + b.Quantity " + ", ToolStock_Info.SendOutQuantity=ToolStock_Info.SendOutQuantity - b.Quantity" + " From (Select ToolID, Sum(Quantity) As Quantity From SendOutDetails_Info a, SendOut_Info b where a.SendOutID=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 (_ID > 0) { sSqlText = sSqlText + " UPDATE SendOut_Info SET SendOutNo='" + FieldValues.GetValue(1) + "'" + ",Description='" + FieldValues.GetValue(3) + "'" + ",ApprovalStatus='" + FieldValues.GetValue(4) + "'"; sSqlText = sSqlText + " WHERE ID=" + _ID + "" + ";"; sSqlText += " ;"; } else { sSqlText = sSqlText + " Insert Into SendOut_Info (TableRecGuid" + ", SendOutNo" + ", SendOutDate" + ", SendOutOrganID" + ", AcceptOrganID" + ", SendOutUserID" + ", Description" + ", ApprovalStatus)" + " Values('" + FieldValues.GetValue(0) + "'" + ",'" + FieldValues.GetValue(1) + "'" + ",GetDate()" + "," + SysGlobal.GetCurrentUserOrganID().ToString() + "," + FieldValues.GetValue(2) + ",'" + SysGlobal.GetCurrentUserID().ToString() + "'" + ",'" + FieldValues.GetValue(3) + "'" + ",'" + FieldValues.GetValue(4) + "')"; sSqlText += " ;"; } sSqlText = sSqlText + DetailsSQL; sSqlText += " Insert Into ToolStock_Info (OrganID, ToolID, Quantity)" + " (Select distinct " + SysClass.SysGlobal.GetCurrentUserOrganID().ToString() + ", ToolID, 0 From SendOutDetails_Info Where Status=0 " + " And SendOutID In (Select ID From SendOut_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.SendOutQuantity=ToolStock_Info.SendOutQuantity + b.Quantity" + " From (Select ToolID, Sum(Quantity) As Quantity From SendOutDetails_Info a, SendOut_Info b where a.SendOutID=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=7 From " + " SendOutDetails_Info b where b.SendOutID In (Select ID From SendOut_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)); }
//系统登录 public static bool UserLogin(string sOpCode, string sPassword, System.Web.SessionState.HttpSessionState hsSession) { bool bSuccess = false; SqlDataReader sda = GetUserLogin(sOpCode, sPassword); if (sda.Read()) { UserInfo info = new UserInfo(); info.UserID = int.Parse(sda["ID"].ToString()); info.OpGuid = sda["Guid"].ToString(); info.OpCode = sda["OpCode"].ToString(); info.OpName = sda["OpName"].ToString(); //权限移到选择系统界面 //info.UserRolePurview = GetPurviewByUserID(info.UserID); info.Purview = info.UserRolePurview; info.UserRoles = sda["UserRoles"].ToString(); info.IsAdmin = sda["IsAdmin"].ToString() == "1"; if (sda["LastDate"].ToString().Length > 0) { info.LastDate = DateTime.Parse(sda["LastDate"].ToString()).ToString("yyyy年MM月dd日 HH:mm:ss"); } //info.PurviewSystemID = 0; info.LastIp = sda["LastIP"].ToString(); info.LastComputerName = sda["LastComputerName"].ToString(); if (sda["OrganID"].ToString().Length > 0) { info.OrganID = SysClass.SysOrgan.GetWorkShopByOrganID(int.Parse(sda["OrganID"].ToString())); } else { info.OrganID = SysClass.SysOrgan.GetTopOrganID(0); } CyxPack.UserCommonOperation.UserCommonOperation.StoreUserInfo(info); // string IP = HttpContext.Current.Request.UserHostAddress;//获取客户端电脑IP string IP = ""; System.Net.IPAddress[] addressList = Dns.GetHostByName(Dns.GetHostName()).AddressList; IP = addressList[0].ToString(); string ComputerName; ComputerName = System.Net.Dns.GetHostName(); //获取本地计算机的主机名 SysLogs.CreateUserLogin(info.UserID, info.OpCode, info.OpName, "用户登录系统。", IP, ComputerName); SysClass.SysGlobal.CurrentUserOrganID = SysGlobal.GetCurrentUserOrganID(); bSuccess = true; } sda.Close(); return(bSuccess); }
public static int UpdateToolTestInfo(int ToolDetailID, int TestUserID, string TestCode, int TestStatus, string Description) { string sSqlText = "begin"; sSqlText += " Insert Into ToolTest_Info (ToolDetailID, TestCode, TestDate, TestStatus, Description, TestUserID, OrganID, CreateUserID) " + " Values(" + ToolDetailID.ToString() + ", '" + TestCode.ToString() + "', GetDate()," + TestStatus.ToString() + ",'" + Description + "' ," + TestUserID.ToString() + "," + SysGlobal.GetCurrentUserOrganID().ToString() + "," + SysGlobal.GetCurrentUserID().ToString() + "); "; sSqlText += " Update ToolStockDetail_Info Set TestCode='" + TestCode + "', TestStatus=" + TestStatus.ToString() + ", TestDate=GetDate() Where ID=" + ToolDetailID.ToString() + ";"; sSqlText += " end;"; return(DataCommon.QueryData(sSqlText)); }
public static SqlDataReader GetWhereServiceStockDetailToolLstByReader(int CategoryID, string WhereSQL) { string sSQL = "Select c.*, a.ID as ToolDetailID, a.ToolCode, a.TestCode, IsNull(b.ServiceQuantity, 0) As StockQuantity, 0 As OldQuantity, b.StorageLocation from ToolStockDetail_Info a " + " left join ToolStock_Info b on b.Status=0 And a.ToolID=b.ToolID And b.OrganID=" + SysGlobal.GetCurrentUserOrganID().ToString() + " left join Tool_Info c on c.Status=0 And a.ToolID=c.ID " + " Where c.ToolType=" + _NomalToolType.ToString() + " And a.OrganID=" + SysGlobal.GetCurrentUserOrganID().ToString() + " And a.Status=0" + WhereSQL; if (CategoryID > 0) { sSQL = sSQL + " And c.CategoryID=" + CategoryID.ToString(); } sSQL = sSQL + " Order By a.SortID"; return(DataCommon.GetDataByReader(sSQL)); }
//更新添加工具档案信息 public static int UpdateSingleToolOrder(int _ID, string[] FieldValues, string DetailsSQL) { string sSqlText = "begin"; if (_ID > 0) { sSqlText = sSqlText + " UPDATE ToolOrder_Info SET OrderNo='" + FieldValues.GetValue(1) + "',OrderType=" + FieldValues.GetValue(2) + ",Description='" + FieldValues.GetValue(3) + "'" + ",ApprovalStatus='" + FieldValues.GetValue(4) + "'" + ",TotalAmount=" + FieldValues.GetValue(5); sSqlText = sSqlText + " WHERE ID=" + _ID + "" + ";"; sSqlText += " ;"; } else { //sSqlText += " if not exists(Select OrganID, PurchaseYear From PurchaseCredit_Info Where OrganID=" + SysGlobal.GetCurrentUserOrganID().ToString() // + " And PurchaseYear=year(getdate()))" // + " begin" // + " Insert Into PurchaseCredit_Info (OrganID, PurchaseYear) Values (" + SysGlobal.GetCurrentUserOrganID().ToString() + ", year(getdate()));" // + " end;"; sSqlText = sSqlText + " Insert Into ToolOrder_Info (TableRecGuid" + ", OrderNo" + ", OrderDate" + ", OrderType" + ", OrganID" + ", OrderUserID" + ", Description" + ", ApprovalStatus" + ", TotalAmount)" + " Values('" + FieldValues.GetValue(0) + "'" + ",'" + FieldValues.GetValue(1) + "'" + ",GetDate()" + "," + FieldValues.GetValue(2) + "," + SysGlobal.GetCurrentUserOrganID().ToString() + ",'" + SysGlobal.GetCurrentUserID().ToString() + "'" + ",'" + FieldValues.GetValue(3) + "'" + ",'" + FieldValues.GetValue(4) + "'" + "," + FieldValues.GetValue(5) + ")"; sSqlText = sSqlText + " ;"; } sSqlText = sSqlText + DetailsSQL + " end;"; return(DataCommon.QueryData(sSqlText)); }
//添加修改检验单 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 UpdateSinglePurchaseOrder(int _ID, string[] FieldValues, string DetailsSQL) { string sSqlText = "begin"; if (_ID > 0) { sSqlText = sSqlText + " UPDATE PurchaseOrder_Info SET OrderNo='" + FieldValues.GetValue(1) + "',SupplierID=" + FieldValues.GetValue(2) + ",Description='" + FieldValues.GetValue(3) + "'" + ",ApprovalStatus='" + FieldValues.GetValue(4) + "'" + ",TotalAmount=" + FieldValues.GetValue(5); sSqlText = sSqlText + " WHERE ID=" + _ID + "" + ";"; sSqlText += " ;"; } else { sSqlText = sSqlText + " Insert Into PurchaseOrder_Info (TableRecGuid" + ", OrderNo" + ", OrderDate" + ", SupplierID" + ", OrderOrganID" + ", OrderUserID" + ", Description" + ", ApprovalStatus" + ", TotalAmount)" + " Values('" + FieldValues.GetValue(0) + "'" + ",'" + FieldValues.GetValue(1) + "'" + ",GetDate()" + "," + FieldValues.GetValue(2) + "," + SysGlobal.GetCurrentUserOrganID().ToString() + ",'" + SysGlobal.GetCurrentUserID().ToString() + "'" + ",'" + FieldValues.GetValue(3) + "'" + ",'" + FieldValues.GetValue(4) + "'" + "," + FieldValues.GetValue(5) + ")"; sSqlText += " ;"; } sSqlText = sSqlText + DetailsSQL + " end;"; return(DataCommon.QueryData(sSqlText)); }
//更新添加工具档案信息 public static int UpdateSingleInventory(int _InventoryID, string[] FieldValues, string DetailsSQL) { string sSqlText = "begin"; if (_InventoryID > 0) { sSqlText = sSqlText + " UPDATE Inventory_Info SET InventoryNo='" + FieldValues.GetValue(1) + "'" + ",InventoryUserID=" + FieldValues.GetValue(2) + "" + ",Description='" + FieldValues.GetValue(3) + "'" + ",ApprovalStatus='" + FieldValues.GetValue(4) + "'"; sSqlText = sSqlText + " WHERE ID=" + _InventoryID + "" + ";"; sSqlText += " ;"; } else { sSqlText = sSqlText + " Insert Into Inventory_Info (TableRecGuid" + ", InventoryNo" + ", InventoryDate" + ", OrganID" + ", InventoryUserID" + ", CreateUserID" + ", Description" + ", ApprovalStatus)" + " Values('" + FieldValues.GetValue(0) + "'" + ",'" + FieldValues.GetValue(1) + "'" + ",GetDate()" + "," + SysGlobal.GetCurrentUserOrganID().ToString() + "," + FieldValues.GetValue(2) + "" + "," + SysGlobal.GetCurrentUserID().ToString() + "" + ",'" + FieldValues.GetValue(3) + "'" + "," + FieldValues.GetValue(4) + ")"; sSqlText = sSqlText + " ;"; } sSqlText = sSqlText + DetailsSQL + " end;"; return(DataCommon.QueryData(sSqlText)); }
public static DataSet GetBorrowDetailsLstByDataSet(int OrganID, string WhereSQL) { string sSQL = "Select a.* , DateDiff(Day, ConsumeDate, GetDate()) As Day, b.ToolID, b.Quantity, c.ToolNo, c.ToolName, c.AliasesName, c.Specification, c.MaterialCode,c.Unit " + ", IsNull(d.OrganName,'') as OrganName, IsNull(e.OpName, '') as ConsumeOpName" + " from Consume_Info a" + " left join SysOrgan_Info d on d.Status=0 And d.ID=a.OrganID" + " left join SysUser_Info e on e.Status=0 And e.ID=a.ConsumeUserID" + ", ConsumeDetails_Info b " + " left join Tool_Info c on c.Status=0 And c.ToolType=" + SysClass.SysTool._NomalToolType.ToString() + " and b.ToolID=c.ID" + " Where IsNull(a.OrganID,0)=" + OrganID.ToString() + " And a.ID=b.ConsumeID And a.ConsumeType=" + SysClass.SysBorrow.ConsumeType_BorrowValue.ToString() + " And a.Status=0 And DateDiff(Day, ConsumeDate, GetDate())>" + GetBorrowValueByOrganID(SysGlobal.GetCurrentUserOrganID()).ToString() + WhereSQL; sSQL = sSQL + " Order By a.ConsumeDate"; return(DataCommon.GetDataByDataSet(sSQL)); }
//设置预警 public static int UpdateToolInitInfo(int _ToolID, int _OrganID, int InitQuantity, string StorageLocation, string Description) { string sSql = "if Exists(Select 1 from ToolStock_Info Where ToolID=" + _ToolID.ToString() + " And OrganID=" + _OrganID.ToString() + ")"; sSql += " begin"; sSql += " update ToolStock_Info Set Quantity=IsNull(Quantity,0) -InitQuantity Where ToolID=" + _ToolID.ToString() + " And OrganID=" + _OrganID.ToString() + " "; sSql += " update ToolStock_Info Set InitQuantity='" + InitQuantity.ToString() + "', StorageLocation='" + StorageLocation.ToString() + "' ,Quantity=IsNull(Quantity,0) + " + InitQuantity.ToString() + ", Description='" + Description + "' Where ToolID=" + _ToolID.ToString() + " And OrganID=" + _OrganID.ToString() + " "; sSql += "end " + " else " + " begin "; sSql += " Insert into ToolStock_Info (ToolID,OrganID, InitQuantity, Quantity, StorageLocation, Description) Values(" + _ToolID.ToString() + ", " + _OrganID.ToString() + ", " + InitQuantity.ToString() + ", " + InitQuantity.ToString() + ", '" + StorageLocation.ToString() + "', '" + Description.ToString() + "') " + " end; "; if (GetIsCodeByToolID(_ToolID)) { int _OldInitQuantity = GetInitQuantityByReader(_OrganID, _ToolID); string _ToolNo = GetToolNoByToolID(_ToolID); int _ToolMax = GetMaxNumByToolID(_ToolID); for (int i = 1; i <= InitQuantity - _OldInitQuantity; i++) { sSql += " Insert Into ToolStockDetail_Info (OrganID, ToolID, ToolCode) Values(" + SysGlobal.GetCurrentUserOrganID().ToString() + "," + _ToolID.ToString() + ",'" + GetToolCodeByToolNo(_ToolNo, _ToolMax + i) + "');"; } sSql += GetUpdateAddNumSQL(_ToolID, InitQuantity); } return(DataCommon.QueryData(sSql)); }
public static DataSet GetBackTestDetailsLstByDataSet(int BackTestID, string WhereSQL) { string sSQL = "Select a.*, b.ToolNo, b.ToolName, b.AliasesName," + " b.Specification, b.MaterialCode,b.Unit, IsNull(c.TestQuantity,0) As StockQuantity, a.Quantity As OldQuantity " + ", c.StorageLocation, d.ToolCode, d.TestCode" + " from BackTestDetails_Info a " + " left join Tool_Info b on b.Status=0 And b.ToolType=" + SysClass.SysTool._NomalToolType.ToString() + " and a.ToolID=b.ID" + " left Join ToolStock_Info c on c.Status=0 And c.OrganID=" + SysGlobal.GetCurrentUserOrganID().ToString() + " and c.ToolID=a.ToolID" + " left join ToolStockDetail_Info d on d.Status=0 And a.ToolDetailID=d.ID" + " Where IsNull(a.BackTestID,0)=" + BackTestID.ToString() + " And a.Status=0" + WhereSQL; sSQL = sSQL + " Order By b.ToolNo"; return(DataCommon.GetDataByDataSet(sSQL)); }
public static int UpdateApprovalToolOut(string MainTableName, string IDs, int ApprovalStatus, string Description) { string sSqlText = "begin"; sSqlText += " Insert Into ApprovalLog_Info (MainTableName, MainID, ApprovalStatus, ApprovalUserID, ApprovalTime, Description) " + " (Select '" + MainTableName + "', ID, " + ApprovalStatus.ToString() + "," + SysGlobal.GetCurrentUserID().ToString() + ", GetDate(), '" + Description + "' From ToolOut_info Where Status=0 And ID In (" + IDs + ")); "; sSqlText += " Update ToolOut_info Set ApprovalStatus=" + ApprovalStatus.ToString() + " Where Status=0 And ID In (" + IDs + ");"; if (ApprovalStatus == ToolOut_ApprovalIsOK) { 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 (" + IDs.ToString() + ")" + " 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 (Select ToolID, Sum(Quantity) As Quantity From ToolOutDetails_Info where " + " ToolOutID in (" + IDs.ToString() + ") 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 (" + IDs.ToString() + ") 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.ToolOutQuantity=ToolStock_Info.ToolOutQuantity + b.Quantity" // + " From " // + " (select a.ToolID, Sum(a.Quantity) As Quantity From ToolOutDetails_Info a, ToolOut_Info b " // + " where a.ToolOutID=b.ID And IsNull(b.ApprovalStatus,0)=1 And b.ID in (" + IDs.ToString() + ")" // + " group by a.ToolID) b where ToolStock_Info.ToolID=b.ToolID"; } sSqlText += " end;"; return(DataCommon.QueryData(sSqlText)); }
//select public static DataSet GetAllinter(int modeID, int OrganID) { string Ssql = "select a.*,b.ConsumeNo,c.SerialNum,d.Specification,d.AliasesName,e.OrganName " + " from InterPhoneCarryDetail_Info a " + " left join InterPhoneCarry_Info b on a.ApplyListID=b.ID " + " left join Interphone_Info c on a.InterID=c.ID " + " left join Tool_Info d on c.ModeID=D.ID " + " left join SysOrgan_Info e on c.OrgainID=e.ID" + " where b.ApprovalStatus=1 and a.IsReturn=1 And b.OrganID=" + SysGlobal.GetCurrentUserOrganID().ToString(); if (modeID > 0) { Ssql = Ssql + " and c.ModeID=" + modeID + ""; } Ssql = Ssql + Ssql; return(DataCommon.GetDataByDataSet(Ssql)); }
public static DataSet GetSendOutDetailsLstByDataSet(int _SendOutID, string WhereSQL) { string sSQL = "select a.*, b.ToolNo, b.ToolName, b.AliasesName, b.Specification" + " , b.MaterialCode, b.Unit, c.StorageLocation, IsNull(c.Quantity, 0) As StockQuantity, 0 As OldQuantity " + ", d.ToolCode, d.TestCode" + " from SendOutDetails_Info a" + " left join Tool_Info b on b.Status=0 And a.ToolID=b.ID" + " left join ToolStock_Info c on c.Status=0 And c.OrganID=" + SysGlobal.GetCurrentUserOrganID().ToString() + " And b.ID=c.ToolID" + " left join ToolStockDetail_Info d on d.Status=0 And d.OrganID=" + SysGlobal.GetCurrentUserOrganID().ToString() + " And d.ID=a.ToolDetailID" + " Where a.Status=0 " + WhereSQL; if (_SendOutID > 0) { sSQL += " And IsNull(a.SendOutID,0)=" + _SendOutID.ToString(); } sSQL = sSQL + " Order By b.ToolNo"; return(DataCommon.GetDataByDataSet(sSQL)); }
public static int UpdateDeliveryShiping(string MainTableName, string DeliveryIDs, int ShipingStatus, string Description) { string sSqlText = "begin"; string sWhereSQL = "And IsNull(a.DeliveryID,0) in (" + DeliveryIDs + ") And IsNull(b.IsCode,0)=1"; SqlDataReader sdr = GetDeliveryDetailsLstByReader(sWhereSQL); while (sdr.Read()) { int _ToolID = int.Parse(sdr["ToolID"].ToString()); int _Quantity = int.Parse(sdr["Quantity"].ToString()); if (SysTool.GetIsCodeByToolID(_ToolID)) { string _ToolNo = SysTool.GetToolNoByToolID(_ToolID); int _ToolMax = SysTool.GetMaxNumByToolID(_ToolID); for (int i = 1; i <= _Quantity; i++) { sSqlText += " Insert Into ToolStockDetail_Info (OrganID, DeliveryID, ToolID, ToolCode) Values(" + SysGlobal.GetCurrentUserOrganID().ToString() + "," + sdr["DeliveryID"].ToString() + "," + _ToolID.ToString() + ",'" + SysTool.GetToolCodeByToolNo(_ToolNo, _ToolMax + i) + "');"; } sSqlText += SysTool.GetUpdateAddNumSQL(_ToolID, _Quantity); } } sdr.Close(); sSqlText += " Insert Into ApprovalLog_Info (MainTableName, MainID, ApprovalStatus, ApprovalUserID, ApprovalTime, Description) " + " (Select '" + MainTableName + "', ID, " + ShipingStatus.ToString() + "," + SysGlobal.GetCurrentUserID().ToString() + ", GetDate(), '" + Description + "' From Delivery_Info Where Status=0 And ShipingStatus=1 And ID In (" + DeliveryIDs + ")); "; sSqlText += " Update Delivery_Info Set ShipingStatus=" + ShipingStatus.ToString() + " Where Status=0 And ShipingStatus=1 And ID In (" + DeliveryIDs + ");"; sSqlText += " Update PurchaseOrderDetails_Info Set DeliveryDetailID=DeliveryDetails_Info.ID, ShipingStatus=" + ShipingStatus.ToString() + " From DeliveryDetails_Info Where DeliveryDetails_Info.DeliveryID in (Select ID From Delivery_Info Where ID in (" + DeliveryIDs + ")) " + " And PurchaseOrderDetails_Info.ToolID=DeliveryDetails_Info.ToolID" + " And PurchaseOrderDetails_Info.DeliveryDetailID>0;"; sSqlText += " Insert Into ToolStock_Info (OrganID, ToolID, Quantity)" + " (Select " + SysClass.SysGlobal.GetCurrentUserOrganID().ToString() + ", ToolID, 0 From DeliveryDetails_Info Where Status=0 " + " And DeliveryID In (" + DeliveryIDs + ")" + " And ToolID not in (Select ToolID From ToolStock_Info Where OrganID=" + SysClass.SysGlobal.GetCurrentUserOrganID().ToString() + ") Group By ToolID);"; sSqlText += " update ToolStock_Info Set ToolStock_Info.Quantity=ToolStock_Info.Quantity+b.Quantity " + " From (Select ToolID, Sum(Quantity) As Quantity From DeliveryDetails_Info where DeliveryID In (" + DeliveryIDs + ") Group By ToolID) b Where ToolStock_Info.ToolID=b.ToolID" + " And ToolStock_Info.OrganID=" + SysClass.SysGlobal.GetCurrentUserOrganID() + ";"; sSqlText += " end;"; return(DataCommon.QueryData(sSqlText)); }
//更新添加工具档案信息 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)); }
public static SqlDataReader GetSendOutDetailsLstByReader(string WhereSQL) { string sSQL = "Select a.SendOutID, a.ToolID, b.ToolNo, b.ToolName, b.AliasesName" + " , c.StorageLocation, IsNull(c.Quantity, 0) As StockQuantity" + " , 0 As OldQuantity, b.Specification, b.MaterialCode, b.Unit, a.Quantity" + " from SendOutDetails_Info a" + " left join Tool_Info b on b.Status=0 and a.ToolID=b.ID " + " left join ToolStock_Info c on c.Status=0 And c.OrganID=" + SysGlobal.GetCurrentUserOrganID().ToString() + " And b.ID=c.ToolID " + " Where a.Status=0 " + WhereSQL + " Order By b.ToolNo"; return(DataCommon.GetDataByReader(sSQL)); }