public static int AddUploadFiles(int MasterTableType, string MasterTableRecGuid, string sSubCategory, string UploadFileName, string FileName, string FileExt, string FileSize, string sDetailSQL) { string sUpdateSQL = " Begin"; sUpdateSQL += " INSERT INTO SysUploadFile_Info (MasterTableType" + ", MasterTableRecGuid" + ",SubCategory" + ",UploadFileName" + ",FileName" + ",FileExt" + ",FileSize" + ",UserID) " + "VALUES(" + MasterTableType.ToString() + "" + ",'" + MasterTableRecGuid + "'" + ",'" + sSubCategory + "'" + ",'" + UploadFileName + "'" + ",'" + FileName + "'" + ",'" + FileExt + "'" + "," + FileSize + "," + SysGlobal.GetCurrentUserID().ToString() + ");"; sUpdateSQL += sDetailSQL + " End;"; return(DataCommon.QueryData(sUpdateSQL)); }
public static int UpdateApprovalToolOrder(string MainTableName, string OrderIDs, 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 ToolOrder_Info Where Status=0 And ID In (" + OrderIDs + ")); "; sSqlText += " Update ToolOrder_Info Set ApprovalStatus=" + ApprovalStatus.ToString() + ", ApprovalUserID=" + SysGlobal.GetCurrentUserID().ToString() + ", ApprovalTime=GetDate() " + " Where Status=0 And ID In (" + OrderIDs + ");"; if (ApprovalStatus == DepotTool_ApprovalIsOK) { sSqlText += "Update PurchaseCredit_Info Set UsedTotalCredit=UsedTotalCredit+b.TotalAmount" + ",UsedCredit1=IsNull(UsedCredit1,0)+(Case Month(b.OrderDate) when 1 then b.TotalAmount else 0 end)" + ",UsedCredit2=IsNull(UsedCredit2,0)+(Case Month(b.OrderDate) when 2 then b.TotalAmount else 0 end)" + ",UsedCredit3=IsNull(UsedCredit3,0)+(Case Month(b.OrderDate) when 3 then b.TotalAmount else 0 end)" + ",UsedCredit4=IsNull(UsedCredit4,0)+(Case Month(b.OrderDate) when 4 then b.TotalAmount else 0 end)" + ",UsedCredit5=IsNull(UsedCredit5,0)+(Case Month(b.OrderDate) when 5 then b.TotalAmount else 0 end)" + ",UsedCredit6=IsNull(UsedCredit6,0)+(Case Month(b.OrderDate) when 6 then b.TotalAmount else 0 end)" + ",UsedCredit7=IsNull(UsedCredit7,0)+(Case Month(b.OrderDate) when 7 then b.TotalAmount else 0 end)" + ",UsedCredit8=IsNull(UsedCredit8,0)+(Case Month(b.OrderDate) when 8 then b.TotalAmount else 0 end)" + ",UsedCredit9=IsNull(UsedCredit9,0)+(Case Month(b.OrderDate) when 9 then b.TotalAmount else 0 end)" + ",UsedCredit10=IsNull(UsedCredit10,0)+(Case Month(b.OrderDate) when 10 then b.TotalAmount else 0 end)" + ",UsedCredit11=IsNull(UsedCredit11,0)+(Case Month(b.OrderDate) when 11 then b.TotalAmount else 0 end)" + ",UsedCredit12=IsNull(UsedCredit12,0)+(Case Month(b.OrderDate) when 12 then b.TotalAmount else 0 end)" + " From ToolOrder_Info b Where b.Status=0 And OrderType=0 And b.ID In (" + OrderIDs + ")" + " And PurchaseCredit_Info.OrganID=b.OrganID And PurchaseCredit_Info.PurchaseYear=Year(b.OrderDate);"; } sSqlText += " end;"; return(DataCommon.QueryData(sSqlText)); }
//---新增、修改订单 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 int UpdateSingleRepairFee(int _ID, string[] FieldValues) { string sSqlText = " begin"; sSqlText += " Update PurchaseCredit_Info Set UsedTotalCredit=UsedTotalCredit-b.RepairAmount" + ",UsedCredit1=IsNull(UsedCredit1,0)+(Case Month(b.RepairDate) when 1 then b.RepairAmount else 0 end)" + ",UsedCredit2=IsNull(UsedCredit2,0)+(Case Month(b.RepairDate) when 2 then b.RepairAmount else 0 end)" + ",UsedCredit3=IsNull(UsedCredit3,0)+(Case Month(b.RepairDate) when 3 then b.RepairAmount else 0 end)" + ",UsedCredit4=IsNull(UsedCredit4,0)+(Case Month(b.RepairDate) when 4 then b.RepairAmount else 0 end)" + ",UsedCredit5=IsNull(UsedCredit5,0)+(Case Month(b.RepairDate) when 5 then b.RepairAmount else 0 end)" + ",UsedCredit6=IsNull(UsedCredit6,0)+(Case Month(b.RepairDate) when 6 then b.RepairAmount else 0 end)" + ",UsedCredit7=IsNull(UsedCredit7,0)+(Case Month(b.RepairDate) when 7 then b.RepairAmount else 0 end)" + ",UsedCredit8=IsNull(UsedCredit8,0)+(Case Month(b.RepairDate) when 8 then b.RepairAmount else 0 end)" + ",UsedCredit9=IsNull(UsedCredit9,0)+(Case Month(b.RepairDate) when 9 then b.RepairAmount else 0 end)" + ",UsedCredit10=IsNull(UsedCredit10,0)+(Case Month(b.RepairDate) when 10 then b.RepairAmount else 0 end)" + ",UsedCredit11=IsNull(UsedCredit11,0)+(Case Month(b.RepairDate) when 11 then b.RepairAmount else 0 end)" + ",UsedCredit12=IsNull(UsedCredit12,0)+(Case Month(b.RepairDate) when 12 then b.RepairAmount else 0 end)" + " From RepairFee_Info b Where b.Status=0 And b.ID = " + _ID.ToString() + " And PurchaseCredit_Info.OrganID=b.OrganID And PurchaseCredit_Info.PurchaseYear=Year(b.RepairDate);"; if (_ID > 0) { sSqlText += " UPDATE RepairFee_Info SET OrganID=" + FieldValues.GetValue(0) + ", RepairDate='" + FieldValues.GetValue(1) + "'" + ",RepairAmount=" + FieldValues.GetValue(2) + ",Description='" + FieldValues.GetValue(3) + "'"; sSqlText += sSqlText + " WHERE ID=" + _ID + "" + ";"; } else { sSqlText += " Insert Into RepairFee_Info ( OrganID, RepairDate, RepairAmount, CreateUserID, Description) Values(" + FieldValues.GetValue(0) + ",'" + FieldValues.GetValue(1) + "'," + FieldValues.GetValue(2) + "," + SysGlobal.GetCurrentUserID().ToString() + ",'" + FieldValues.GetValue(3) + "')" + ";"; } sSqlText += " Update PurchaseCredit_Info Set UsedTotalCredit=UsedTotalCredit+b.RepairAmount" + ",UsedCredit1=IsNull(UsedCredit1,0)+(Case Month(b.RepairDate) when 1 then b.RepairAmount else 0 end)" + ",UsedCredit2=IsNull(UsedCredit2,0)+(Case Month(b.RepairDate) when 2 then b.RepairAmount else 0 end)" + ",UsedCredit3=IsNull(UsedCredit3,0)+(Case Month(b.RepairDate) when 3 then b.RepairAmount else 0 end)" + ",UsedCredit4=IsNull(UsedCredit4,0)+(Case Month(b.RepairDate) when 4 then b.RepairAmount else 0 end)" + ",UsedCredit5=IsNull(UsedCredit5,0)+(Case Month(b.RepairDate) when 5 then b.RepairAmount else 0 end)" + ",UsedCredit6=IsNull(UsedCredit6,0)+(Case Month(b.RepairDate) when 6 then b.RepairAmount else 0 end)" + ",UsedCredit7=IsNull(UsedCredit7,0)+(Case Month(b.RepairDate) when 7 then b.RepairAmount else 0 end)" + ",UsedCredit8=IsNull(UsedCredit8,0)+(Case Month(b.RepairDate) when 8 then b.RepairAmount else 0 end)" + ",UsedCredit9=IsNull(UsedCredit9,0)+(Case Month(b.RepairDate) when 9 then b.RepairAmount else 0 end)" + ",UsedCredit10=IsNull(UsedCredit10,0)+(Case Month(b.RepairDate) when 10 then b.RepairAmount else 0 end)" + ",UsedCredit11=IsNull(UsedCredit11,0)+(Case Month(b.RepairDate) when 11 then b.RepairAmount else 0 end)" + ",UsedCredit12=IsNull(UsedCredit12,0)+(Case Month(b.RepairDate) when 12 then b.RepairAmount else 0 end)" + " From RepairFee_Info b Where b.Status=0 And b.ID = " + _ID.ToString() + " And PurchaseCredit_Info.OrganID=b.OrganID And PurchaseCredit_Info.PurchaseYear=Year(b.RepairDate);"; sSqlText += " end;"; return(DataCommon.QueryData(sSqlText)); }
//审批保存 public static int UpdateApprovalToolOrder(string MainTableName, string OrderIDs, 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 WorksShopInterOrder_Info Where Status=0 And ID In (" + OrderIDs + ")); "; sSqlText += " Update WorksShopInterOrder_Info Set Isfinish=" + ApprovalStatus.ToString() + ", ApprovalUserID=" + SysGlobal.GetCurrentUserID().ToString() + ", ApprovalTime=GetDate() " + " Where Status=0 And ID In (" + OrderIDs + ");"; sSqlText += " end;"; return(DataCommon.QueryData(sSqlText)); }
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 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 UpUserShiply(int _ID, string[] FieldValues, string Sql) { string sSqlText = ""; if (_ID > 0) { sSqlText = sSqlText + " UPDATE InterphoneShip_Info SET ShipNo='" + FieldValues.GetValue(1) + "'" + ", Isfinish=" + FieldValues.GetValue(5) + ", Description='" + FieldValues.GetValue(4) + "'"; sSqlText = sSqlText + " WHERE ID=" + _ID + "" + ";"; sSqlText = sSqlText + Sql + " ;"; } else { sSqlText = sSqlText + " Insert Into InterphoneShip_Info (TableRecGuid" + ", ShipNo" + ", Quantity" + ", ActionUserID" + ", Shipdate" + ", ShipOrganID" + ", Isfinish" + ", OrderInfoID" + ", Mode" + ", Description)" + " Values('" + FieldValues.GetValue(0) + "'" + ",'" + FieldValues.GetValue(1) + "'" + ",'" + FieldValues.GetValue(2) + "'" + "," + SysGlobal.GetCurrentUserID().ToString() + "" + ",GetDate()" + "," + FieldValues.GetValue(3) + "" + "," + FieldValues.GetValue(5) + "" + "," + FieldValues.GetValue(6) + "" + ",'" + FieldValues.GetValue(7) + "'" + ",'" + FieldValues.GetValue(4) + "')"; sSqlText = sSqlText + Sql + " ;"; } 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 int UpdateTestCancelStatus(string MainTableName, string TestIDs, int CancelStatus, string Description) { string sSqlText = "begin"; sSqlText += " Insert Into ApprovalLog_Info (MainTableName, MainID, ApprovalStatus, ApprovalUserID, ApprovalTime, Description) " + " (Select '" + MainTableName + "', ID, " + CancelStatus.ToString() + "," + SysGlobal.GetCurrentUserID().ToString() + ", GetDate(), '" + Description + "' From ToolTest_Info Where Status=0 And ProcessStatus=0 And ID In (" + TestIDs + ")); "; if (CancelStatus == ToolTest_ProcessIsCancel) { sSqlText += " Update ToolStock_Info Set ToolStock_Info.TestQuantity=ToolStock_Info.TestQuantity-b.ACount" + ", ToolStock_Info.CancelQuantity=ToolStock_Info.CancelQuantity+b.ACount" + " From (Select b.ToolID, Count(1) As ACount From ToolTest_Info a,ToolStockDetail_Info b " + " Where a.Status=0 And a.ToolDetailID=b.ID And b.OrganID=" + SysClass.SysGlobal.GetCurrentUserOrganID().ToString() + " And a.ID In (" + TestIDs + ") Group By b.ToolID) b" + " Where ToolStock_Info.Status=0 And ToolStock_Info.ToolID=b.ToolID ;"; sSqlText += " Update ToolStockDetail_Info Set ToolStatus=5, TestStatus=" + CancelStatus + " Where OrganID=" + SysClass.SysGlobal.GetCurrentUserOrganID().ToString() + " And ID in (Select ToolDetailID From ToolTest_Info Where Status=0 And ID in (" + TestIDs + "));"; } else { sSqlText += " Update ToolStockDetail_Info Set ToolStatus=5, TestStatus=" + CancelStatus + " Where OrganID=" + SysClass.SysGlobal.GetCurrentUserOrganID().ToString() + " And ID in (Select ToolDetailID From ToolTest_Info Where Status=0 And ID in (" + TestIDs + "));"; } sSqlText += " Update ToolTest_Info Set ProcessStatus=" + CancelStatus.ToString() + " Where Status=0 And ProcessStatus=0 And ID In (" + TestIDs + ");"; sSqlText += " end;"; return(DataCommon.QueryData(sSqlText)); }
public static int UpdateCancelStatus(string MainTableName, string ConsumeDetailIDs, int CancelStatus, string Description) { string sSqlText = "begin"; sSqlText += " Insert Into ApprovalLog_Info (MainTableName, MainID, ApprovalStatus, ApprovalUserID, ApprovalTime, Description) " + " (Select '" + MainTableName + "', ID, " + CancelStatus.ToString() + "," + SysGlobal.GetCurrentUserID().ToString() + ", GetDate(), '" + Description + "' From ConsumeDetails_Info Where Status=0 And ReturnStatus=0 And ID In (" + ConsumeDetailIDs + ")); "; sSqlText += " Update ConsumeDetails_Info Set ReturnStatus=" + CancelStatus.ToString() + " Where Status=0 And ReturnStatus=0 And ID In (" + ConsumeDetailIDs + ");"; sSqlText += " Update ToolStockDetail_Info Set ToolStatus=5 Where ID in (Select ToolDetailID From ConsumeDetails_Info Where Status=0 And ID In (" + ConsumeDetailIDs + "));"; sSqlText += " update ToolStock_Info Set ToolStock_Info.CancelQuantity=ToolStock_Info.CancelQuantity+b.Quantity " + ", ToolStock_Info.ConsumeQuantity=ToolStock_Info.ConsumeQuantity - b.Quantity " + " From (select a.ToolID, Sum(a.Quantity) As Quantity from ConsumeDetails_Info a, Consume_Info b Where a.ConsumeID=b.ID And ConsumeType=" + SysClass.SysConsume.ConsumeType_ConsumeValue.ToString() + " And a.ID In (" + ConsumeDetailIDs + ")" + " Group By a.ToolID) b where ToolStock_Info.ToolID=b.ToolID" + " And ToolStock_Info.OrganID=" + SysClass.SysGlobal.GetCurrentUserOrganID(); sSqlText += " update ToolStock_Info Set ToolStock_Info.CancelQuantity=ToolStock_Info.CancelQuantity+b.Quantity " + ", ToolStock_Info.BorrowQuantity=ToolStock_Info.BorrowQuantity - b.Quantity " + " From (select a.ToolID, Sum(a.Quantity) As Quantity from ConsumeDetails_Info a, Consume_Info b Where a.ConsumeID=b.ID And ConsumeType=" + SysClass.SysBorrow.ConsumeType_BorrowValue.ToString() + " And a.ID In (" + ConsumeDetailIDs + ")" + " Group By a.ToolID) b where ToolStock_Info.ToolID=b.ToolID" + " And ToolStock_Info.OrganID=" + SysClass.SysGlobal.GetCurrentUserOrganID(); sSqlText += " end;"; return(DataCommon.QueryData(sSqlText)); }
public static int UpdateApprovalBackTest(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 BackTest_info Where Status=0 And ID In (" + IDs + ")); "; sSqlText += " Update BackTest_info Set ApprovalStatus=" + ApprovalStatus.ToString() + " Where Status=0 And ID In (" + IDs + ");"; if (ApprovalStatus == BackTest_ApprovalIsOK) { sSqlText += " update ToolStock_Info Set ToolStock_Info.Quantity=ToolStock_Info.Quantity-b.Quantity " + ", ToolStock_Info.TestQuantity=ToolStock_Info.TestQuantity + b.Quantity" + " From " + " (select a.ToolID, Sum(a.Quantity) As Quantity From BackTestDetails_Info a, BackTest_Info b " + " where a.BackTestID=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)); }
//更新添加工具档案信息 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 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)); }
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)); }