//---新增、修改订单
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));
}
