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