/// <summary>
/// 根据选择删除多条预算外出库单
/// </summary>
public bool DeleteSWR_Multi(DataTable swwHeadTable)
{
string swwHeadNoListStr = "";
for (int i = 0; i < swwHeadTable.Rows.Count; i++)
{
if (DataTypeConvert.GetBoolean(swwHeadTable.Rows[i]["Select"]))
{
swwHeadNoListStr += string.Format("'{0}',", DataTypeConvert.GetString(swwHeadTable.Rows[i]["SpecialWarehouseReceipt"]));
}
}
swwHeadNoListStr = swwHeadNoListStr.Substring(0, swwHeadNoListStr.Length - 1);
if (!CheckWarehouseState(swwHeadTable, null, swwHeadNoListStr, false, true, true, true))
{
return(false);
}
using (SqlConnection conn = new SqlConnection(BaseSQL.connectionString))
{
conn.Open();
using (SqlTransaction trans = conn.BeginTransaction())
{
try
{
SqlCommand cmd = new SqlCommand("", conn, trans);
string errorText = "";
cmd.CommandText = string.Format("select * from INV_SpecialWarehouseReceiptList where SpecialWarehouseReceipt in ({0})", swwHeadNoListStr);
DataTable tmpTable = new DataTable();
SqlDataAdapter adpt = new SqlDataAdapter(cmd);
adpt.Fill(tmpTable);
//保存日志到日志表中
DataRow[] swwHeadRows = swwHeadTable.Select("select=1");
FrmWarehouseCommonDAO whDAO = new FrmWarehouseCommonDAO();
for (int i = 0; i < swwHeadRows.Length; i++)
{
string logStr = LogHandler.RecordLog_DeleteRow(cmd, "预算外出库单", swwHeadRows[i], "SpecialWarehouseReceipt");
if (!whDAO.IsDeleteWarehouseOrder(cmd, DataTypeConvert.GetDateTime(swwHeadRows[i]["SpecialWarehouseReceiptDate"])))
{
return(false);
}
SqlCommand cmd_proc_cancel = new SqlCommand("", conn, trans);
if (!whDAO.Update_WarehouseNowInfo(cmd_proc_cancel, DataTypeConvert.GetString(swwHeadRows[i]["SpecialWarehouseReceipt"]), 2, out errorText))
{
trans.Rollback();
MessageHandler.ShowMessageBox("预算外出库单删除出库错误--" + errorText);
return(false);
}
}
cmd.CommandText = string.Format("Delete from INV_SpecialWarehouseReceiptList where SpecialWarehouseReceipt in ({0})", swwHeadNoListStr);
cmd.ExecuteNonQuery();
cmd.CommandText = string.Format("Delete from INV_SpecialWarehouseReceiptHead where SpecialWarehouseReceipt in ({0})", swwHeadNoListStr);
cmd.ExecuteNonQuery();
//Set_OrderHead_End(cmd, tmpTable);
trans.Commit();
return(true);
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
finally
{
conn.Close();
}
}
}
}
/// <summary>
/// 根据选择删除多条库存调整单
/// </summary>
public bool DeleteInventoryAdjustments_Multi(DataTable iaHeadTable)
{
string iaHeadNoListStr = "";
for (int i = 0; i < iaHeadTable.Rows.Count; i++)
{
if (DataTypeConvert.GetBoolean(iaHeadTable.Rows[i]["Select"]))
{
iaHeadNoListStr += string.Format("'{0}',", DataTypeConvert.GetString(iaHeadTable.Rows[i]["InventoryAdjustmentsNo"]));
}
}
iaHeadNoListStr = iaHeadNoListStr.Substring(0, iaHeadNoListStr.Length - 1);
if (!CheckWarehouseState(iaHeadTable, null, iaHeadNoListStr, false, true, true, true))
{
return(false);
}
using (SqlConnection conn = new SqlConnection(BaseSQL.connectionString))
{
conn.Open();
using (SqlTransaction trans = conn.BeginTransaction())
{
try
{
SqlCommand cmd = new SqlCommand("", conn, trans);
string errorText = "";
//cmd.CommandText = string.Format("select * from INV_InventoryAdjustmentsList where InventoryAdjustmentsNo in ({0})", iaHeadNoListStr);
//DataTable tmpTable = new DataTable();
//SqlDataAdapter adpt = new SqlDataAdapter(cmd);
//adpt.Fill(tmpTable);
//保存日志到日志表中
DataRow[] headRows = iaHeadTable.Select("select=1");
FrmWarehouseCommonDAO whDAO = new FrmWarehouseCommonDAO();
for (int i = 0; i < headRows.Length; i++)
{
string logStr = LogHandler.RecordLog_DeleteRow(cmd, "库存调整单", headRows[i], "InventoryAdjustmentsNo");
if (!whDAO.IsDeleteWarehouseOrder(cmd, DataTypeConvert.GetDateTime(headRows[i]["InventoryAdjustmentsDate"])))
{
return(false);
}
SqlCommand cmd_proc_cancel = new SqlCommand("", conn, trans);
if (!whDAO.Update_WarehouseNowInfo(cmd_proc_cancel, DataTypeConvert.GetString(headRows[i]["InventoryAdjustmentsNo"]), 2, out errorText))
{
trans.Rollback();
MessageHandler.ShowMessageBox("库存调整单删除入库错误--" + errorText);
return(false);
}
}
//cmd.CommandText = string.Format("Delete from INV_WarehouseNowInfo where Qty = 0 and exists (select * from INV_InventoryAdjustmentsList as list where list.CodeId = INV_WarehouseNowInfo.CodeId and list.CodeFileName = INV_WarehouseNowInfo.CodeFileName and list.RepertoryId = INV_WarehouseNowInfo.RepertoryId and list.LocationId = INV_WarehouseNowInfo.LocationId and list.ProjectNo = INV_WarehouseNowInfo.ProjectNo and list.ShelfId = INV_WarehouseNowInfo.ShelfId and InventoryAdjustmentsNo in ({0}))", iaHeadNoListStr);
//cmd.ExecuteNonQuery();
cmd.CommandText = string.Format("Delete from INV_InventoryAdjustmentsList where InventoryAdjustmentsNo in ({0})", iaHeadNoListStr);
cmd.ExecuteNonQuery();
cmd.CommandText = string.Format("Delete from INV_InventoryAdjustmentsHead where InventoryAdjustmentsNo in ({0})", iaHeadNoListStr);
cmd.ExecuteNonQuery();
//Set_WWHead_End(cmd, tmpTable);
trans.Commit();
return(true);
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
finally
{
conn.Close();
}
}
}
}
/// <summary>
/// 保存预算外出库单
/// </summary>
/// <param name="swrHeadRow">预算外出库单表头数据表</param>
/// <param name="swrListTable">预算外出库单明细数据表</param>
public int SaveSpecialWarehouseReceipt(DataRow swrHeadRow, DataTable swrListTable)
{
using (SqlConnection conn = new SqlConnection(BaseSQL.connectionString))
{
conn.Open();
using (SqlTransaction trans = conn.BeginTransaction())
{
try
{
SqlCommand cmd = new SqlCommand("", conn, trans);
DateTime serverTime = BaseSQL.GetServerDateTime();
FrmWarehouseCommonDAO whDAO = new FrmWarehouseCommonDAO();
if (!whDAO.IsSaveWarehouseOrder(cmd, swrHeadRow, DataTypeConvert.GetDateTime(swrHeadRow["SpecialWarehouseReceiptDate"])))
{
return(0);
}
//if (DataTypeConvert.GetString(swwHeadRow["SpecialWarehouseReceipt"]) == "")//新增
if (swrHeadRow.RowState == DataRowState.Added)//新增
{
string swwNo = BaseSQL.GetMaxCodeNo(cmd, "SR");
swrHeadRow["SpecialWarehouseReceipt"] = swwNo;
swrHeadRow["PreparedIp"] = SystemInfo.HostIpAddress;
for (int i = 0; i < swrListTable.Rows.Count; i++)
{
swrListTable.Rows[i]["SpecialWarehouseReceipt"] = swwNo;
}
}
else//修改
{
if (!CheckWarehouseState(swrHeadRow.Table, swrListTable, string.Format("'{0}'", DataTypeConvert.GetString(swrHeadRow["SpecialWarehouseReceipt"])), false, true, true, true))
{
return(-1);
}
swrHeadRow["ModifierId"] = SystemInfo.user.AutoId;
//swrHeadRow["Modifier"] = SystemInfo.user.EmpName;
swrHeadRow["ModifierIp"] = SystemInfo.HostIpAddress;
swrHeadRow["ModifierTime"] = serverTime;
}
string swrNoStr = DataTypeConvert.GetString(swrHeadRow["SpecialWarehouseReceipt"]);
DataTable dbListTable = new DataTable();
if (swrHeadRow.RowState != DataRowState.Added)
{
cmd.CommandText = string.Format("select CodeId, CodeFileName, head.RepertoryId, head.RepertoryLocationId, ProjectNo, list.ShelfId, Sum(Qty) as Qty from INV_SpecialWarehouseReceiptList as list left join INV_SpecialWarehouseReceiptHead as head on list.SpecialWarehouseReceipt = head.SpecialWarehouseReceipt left join BS_ProjectList on list.ProjectName = BS_ProjectList.ProjectName where list.SpecialWarehouseReceipt = '{0}' group by CodeId, CodeFileName, head.RepertoryId, head.RepertoryLocationId, ProjectNo, list.ShelfId", swrNoStr);
SqlDataAdapter dbListAdapter = new SqlDataAdapter(cmd);
dbListAdapter.Fill(dbListTable);
}
//保存日志到日志表中
string logStr = LogHandler.RecordLog_DataRow(cmd, "预算外出库单", swrHeadRow, "SpecialWarehouseReceipt");
cmd.CommandText = "select * from INV_SpecialWarehouseReceiptHead where 1=2";
SqlDataAdapter adapterHead = new SqlDataAdapter(cmd);
DataTable tmpHeadTable = new DataTable();
adapterHead.Fill(tmpHeadTable);
BaseSQL.UpdateDataTable(adapterHead, swrHeadRow.Table.GetChanges());
cmd.CommandText = "select * from INV_SpecialWarehouseReceiptList where 1=2";
SqlDataAdapter adapterList = new SqlDataAdapter(cmd);
DataTable tmpListTable = new DataTable();
adapterList.Fill(tmpListTable);
BaseSQL.UpdateDataTable(adapterList, swrListTable.GetChanges());
//Set_OrderHead_End(cmd, swwListTable);
if (whDAO.SaveUpdate_WarehouseNowInfo(conn, trans, cmd, swrHeadRow, swrListTable.Copy(), swrNoStr, dbListTable, "预算外出库单", "出库", false) != 1)
{
return(0);
}
if (SystemInfo.InventorySaveApproval)
{
//cmd.CommandText = string.Format("Insert into PUR_OrderApprovalInfo(OrderHeadNo, Approver, ApproverTime) values ('{0}', {1}, '{2}')", swrNoStr, SystemInfo.user.AutoId, serverTime.ToString("yyyy-MM-dd HH:mm:ss"));
//cmd.ExecuteNonQuery();
//logStr = LogHandler.RecordLog_OperateRow(cmd, "预算外出库单", swrHeadRow, "SpecialWarehouseReceipt", "审批", SystemInfo.user.EmpName, serverTime.ToString("yyyy-MM-dd HH:mm:ss"));
new PURDAO.FrmApprovalDAO().InventorySaveApproval(cmd, swrHeadRow, "预算外出库单", "SpecialWarehouseReceipt", swrNoStr, serverTime);
cmd.CommandText = string.Format("Update INV_SpecialWarehouseReceiptHead set WarehouseState=2 where SpecialWarehouseReceipt='{0}'", swrNoStr);
cmd.ExecuteNonQuery();
swrHeadRow["WarehouseState"] = 2;
}
trans.Commit();
swrHeadRow.Table.AcceptChanges();
swrListTable.AcceptChanges();
return(1);
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
finally
{
conn.Close();
}
}
}
}
/// <summary>
/// 保存库存调整单
/// </summary>
/// <param name="IAHeadRow">库存调整单表头数据表</param>
/// <param name="IAListTable">库存调整单明细数据表</param>
public int SaveInventoryAdjustments(DataRow IAHeadRow, DataTable IAListTable)
{
using (SqlConnection conn = new SqlConnection(BaseSQL.connectionString))
{
conn.Open();
using (SqlTransaction trans = conn.BeginTransaction())
{
try
{
SqlCommand cmd = new SqlCommand("", conn, trans);
DateTime serverTime = BaseSQL.GetServerDateTime();
FrmWarehouseCommonDAO whDAO = new FrmWarehouseCommonDAO();
if (!whDAO.IsSaveWarehouseOrder(cmd, IAHeadRow, DataTypeConvert.GetDateTime(IAHeadRow["InventoryAdjustmentsDate"])))
{
return(0);
}
for (int i = 0; i < IAListTable.Rows.Count; i++)
{
if (IAListTable.Rows[i].RowState == DataRowState.Deleted)
{
continue;
}
IAListTable.Rows[i]["InventoryAdjustmentsDate"] = IAHeadRow["InventoryAdjustmentsDate"];
IAListTable.Rows[i]["RepertoryId"] = IAHeadRow["RepertoryId"];
IAListTable.Rows[i]["LocationId"] = IAHeadRow["LocationId"];
IAListTable.Rows[i]["ProjectNo"] = IAHeadRow["ProjectNo"];
}
////检查当前库存数是否满足
//if (!SystemInfo.EnableNegativeInventory && !CheckWarehouseNowInfoBeyondCount(cmd, DataTypeConvert.GetString(IAHeadRow["InventoryMoveNo"]), IAListTable))
//{
// return 0;
//}
//if (DataTypeConvert.GetString(IAHeadRow["InventoryAdjustmentsNo"]) == "")//新增
if (IAHeadRow.RowState == DataRowState.Added)//新增
{
string iaNo = BaseSQL.GetMaxCodeNo(cmd, "IA");
IAHeadRow["InventoryAdjustmentsNo"] = iaNo;
IAHeadRow["CreatorIp"] = SystemInfo.HostIpAddress;
for (int i = 0; i < IAListTable.Rows.Count; i++)
{
IAListTable.Rows[i]["InventoryAdjustmentsNo"] = iaNo;
}
}
else//修改
{
if (!CheckWarehouseState(IAHeadRow.Table, IAListTable, string.Format("'{0}'", DataTypeConvert.GetString(IAHeadRow["InventoryAdjustmentsNo"])), false, true, true, true))
{
return(-1);
}
IAHeadRow["Modifier"] = SystemInfo.user.AutoId;
IAHeadRow["ModifierIp"] = SystemInfo.HostIpAddress;
IAHeadRow["ModifierTime"] = serverTime;
}
string iaNoStr = DataTypeConvert.GetString(IAHeadRow["InventoryAdjustmentsNo"]);
#region 单独提出为通用方法
//string errorText = "";
//int affectedRowNo = 0;
//if (IAHeadRow.RowState != DataRowState.Added)
//{
// SqlCommand cmd_proc_cancel = new SqlCommand("", conn, trans);
// if (!new FrmWarehouseNowInfoDAO().Update_WarehouseNowInfo(cmd_proc_cancel, iaNoStr, 2, out errorText))
// {
// trans.Rollback();
// MessageHandler.ShowMessageBox("库存调整单取消入库错误--" + errorText);
// return 0;
// }
//}
//if (IAHeadRow.RowState == DataRowState.Added)
//{
//}
//else
//{
// cmd.CommandText = string.Format("select CodeFileName, head.RepertoryId, head.LocationId, head.ProjectNo, list.ShelfId, Sum(Qty) as Qty from INV_InventoryAdjustmentsList as list left join INV_InventoryAdjustmentsHead as head on list.InventoryAdjustmentsNo = head.InventoryAdjustmentsNo where list.InventoryAdjustmentsNo = '{0}' group by CodeFileName, head.RepertoryId, head.LocationId, head.ProjectNo, list.ShelfId", iaNoStr);
// SqlDataAdapter dbListAdapter = new SqlDataAdapter(cmd);
// DataTable dbListTable = new DataTable();
// dbListAdapter.Fill(dbListTable);
// DataTable copyNewTable = IAListTable.GetChanges();
// copyNewTable.AcceptChanges();
// foreach (DataRow dbRow in dbListTable.Rows)
// {
// string tmpCodeFileName = DataTypeConvert.GetString(dbRow["CodeFileName"]);
// int tmpRepertoryId = DataTypeConvert.GetInt(dbRow["RepertoryId"]);
// int tmpLocationId = DataTypeConvert.GetInt(dbRow["LocationId"]);
// string tmpProjectNo = DataTypeConvert.GetString(dbRow["ProjectNo"]);
// int tmpShelfId = DataTypeConvert.GetInt(dbRow["ShelfId"]);
// double tmpQty = DataTypeConvert.GetDouble(dbRow["Qty"]);
// double iaSumQty = 0;
// if (tmpQty > 0)
// {
// iaSumQty = DataTypeConvert.GetDouble(copyNewTable.Compute("Sum(Qty)", string.Format("CodeFileName='{0}' and RepertoryId={1} and LocationId={2} and ProjectNo='{3}' and ShelfId={4}", tmpCodeFileName, tmpRepertoryId, tmpLocationId, tmpProjectNo, tmpShelfId)));
// }
// if (tmpQty == iaSumQty)
// {
// }
// else
// {
// cmd.CommandText = string.Format("update INV_WarehouseNowInfo set Qty = Qty + ({5}) where CodeFileName='{0}' and RepertoryId={1} and LocationId={2} and ProjectNo='{3}' and ShelfId={4}", tmpCodeFileName, tmpRepertoryId, tmpLocationId, tmpProjectNo, tmpShelfId, iaSumQty - tmpQty);
// affectedRowNo = cmd.ExecuteNonQuery();
// if (affectedRowNo == 0)
// {
// cmd.CommandText = string.Format("insert into INV_WarehouseNowInfo(CodeFileName, RepertoryId, LocationId, ProjectNo, ShelfId, Qty) values ('{0}', {1}, {2}, '{3}', {4}, {5})", tmpCodeFileName, tmpRepertoryId, tmpLocationId, tmpProjectNo, tmpShelfId, iaSumQty - tmpQty);
// cmd.ExecuteNonQuery();
// }
// }
// DataRow[] drs = copyNewTable.Select(string.Format("CodeFileName='{0}' and RepertoryId={1} and LocationId={2} and ProjectNo='{3}' and ShelfId={4}", tmpCodeFileName, tmpRepertoryId, tmpLocationId, tmpProjectNo, tmpShelfId));
// foreach (DataRow dr in drs)
// {
// copyNewTable.Rows.Remove(dr);
// }
// }
// foreach (DataRow newRow in copyNewTable.Rows)
// {
// string newCodeFileName = DataTypeConvert.GetString(newRow["CodeFileName"]);
// int newRepertoryId = DataTypeConvert.GetInt(newRow["RepertoryId"]);
// int newLocationId = DataTypeConvert.GetInt(newRow["LocationId"]);
// string newProjectNo = DataTypeConvert.GetString(newRow["ProjectNo"]);
// int newShelfId = DataTypeConvert.GetInt(newRow["ShelfId"]);
// double newQty = DataTypeConvert.GetDouble(newRow["Qty"]);
// cmd.CommandText = string.Format("update INV_WarehouseNowInfo set Qty = Qty + ({5}) where CodeFileName='{0}' and RepertoryId={1} and LocationId={2} and ProjectNo='{3}' and ShelfId={4}", newCodeFileName, newRepertoryId, newLocationId, newProjectNo, newShelfId, newQty);
// affectedRowNo = cmd.ExecuteNonQuery();
// if (affectedRowNo == 0)
// {
// cmd.CommandText = string.Format("insert into INV_WarehouseNowInfo(CodeFileName, RepertoryId, LocationId, ProjectNo, ShelfId, Qty) values ('{0}', {1}, {2}, '{3}', {4}, {5})", newCodeFileName, newRepertoryId, newLocationId, newProjectNo, newShelfId, newQty);
// cmd.ExecuteNonQuery();
// }
// }
//}
#endregion
DataTable dbListTable = new DataTable();
if (IAHeadRow.RowState != DataRowState.Added)
{
cmd.CommandText = string.Format("select CodeId, CodeFileName, head.RepertoryId, head.LocationId, head.ProjectNo, list.ShelfId, Sum(Qty) as Qty from INV_InventoryAdjustmentsList as list left join INV_InventoryAdjustmentsHead as head on list.InventoryAdjustmentsNo = head.InventoryAdjustmentsNo where list.InventoryAdjustmentsNo = '{0}' group by CodeId, CodeFileName, head.RepertoryId, head.LocationId, head.ProjectNo, list.ShelfId", iaNoStr);
SqlDataAdapter dbListAdapter = new SqlDataAdapter(cmd);
dbListAdapter.Fill(dbListTable);
}
//保存日志到日志表中
string logStr = LogHandler.RecordLog_DataRow(cmd, "库存调整单", IAHeadRow, "InventoryAdjustmentsNo");
cmd.CommandText = "select * from INV_InventoryAdjustmentsHead where 1=2";
SqlDataAdapter adapterHead = new SqlDataAdapter(cmd);
DataTable tmpHeadTable = new DataTable();
adapterHead.Fill(tmpHeadTable);
BaseSQL.UpdateDataTable(adapterHead, IAHeadRow.Table.GetChanges());
cmd.CommandText = "select * from INV_InventoryAdjustmentsList where 1=2";
SqlDataAdapter adapterList = new SqlDataAdapter(cmd);
DataTable tmpListTable = new DataTable();
adapterList.Fill(tmpListTable);
BaseSQL.UpdateDataTable(adapterList, IAListTable.GetChanges());
//Set_WWHead_End(cmd, IMListTable);
//SqlCommand cmd_proc = new SqlCommand("", conn, trans);
//if (!new FrmWarehouseNowInfoDAO().Update_WarehouseNowInfo(cmd_proc, iaNoStr, 1, out errorText))
//{
// trans.Rollback();
// MessageHandler.ShowMessageBox("库存调整单入库错误--" + errorText);
// return 0;
//}
if (whDAO.SaveUpdate_WarehouseNowInfo(conn, trans, cmd, IAHeadRow, IAListTable.Copy(), iaNoStr, dbListTable, "库存调整单", "入库", true) != 1)
{
return(0);
}
if (SystemInfo.InventorySaveApproval)
{
//cmd.CommandText = string.Format("Insert into PUR_OrderApprovalInfo(OrderHeadNo, Approver, ApproverTime) values ('{0}', {1}, '{2}')", iaNoStr, SystemInfo.user.AutoId, serverTime.ToString("yyyy-MM-dd HH:mm:ss"));
//cmd.ExecuteNonQuery();
//logStr = LogHandler.RecordLog_OperateRow(cmd, "库存调整单", IAHeadRow, "InventoryAdjustmentsNo", "审批", SystemInfo.user.EmpName, serverTime.ToString("yyyy-MM-dd HH:mm:ss"));
new PURDAO.FrmApprovalDAO().InventorySaveApproval(cmd, IAHeadRow, "库存调整单", "InventoryAdjustmentsNo", iaNoStr, serverTime);
cmd.CommandText = string.Format("Update INV_InventoryAdjustmentsHead set WarehouseState=2 where InventoryAdjustmentsNo='{0}'", iaNoStr);
cmd.ExecuteNonQuery();
IAHeadRow["WarehouseState"] = 2;
}
trans.Commit();
IAHeadRow.Table.AcceptChanges();
IAListTable.AcceptChanges();
return(1);
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
finally
{
conn.Close();
}
}
}
}