public void insert(ArrayList orderDetailsRecords)
{
if (orderDetailsRecords.Count <= 0)
{
return;
}
else
{
try
{
for (int i = 0; i < orderDetailsRecords.Count; i++)
{
MaterielOutOtherOrderDetailsTable record = (MaterielOutOtherOrderDetailsTable)orderDetailsRecords[i];
if (i == 0 && checkBillIsExist(record.billNumber))
{
// 根据单据编号，判断库中是否已经存在该单据 如果存在单据首先删除单据，然后再执行插入操作
delete(record.billNumber);
}
string insert = "INSERT INTO [dbo].[WAREHOUSE_MANAGEMENT_OUT_OTHER_DETAILS]([ROW_NUMBER],[MATERIEL_ID],[BILL_NUMBER]";
insert += ",[PRICE],[VALUE],[NOTE])VALUES(";
insert += "'" + record.rowNumber + "',";
insert += record.materielID + ",";
insert += "'" + record.billNumber + "',";
insert += record.price + ",";
insert += record.value + ",";
insert += "'" + record.note + "'";
insert += ")";
try
{
DatabaseAccessFactoryInstance.Instance.ExecuteCommand(FormMain.DB_NAME, insert);
}
catch (Exception error)
{
MessageBoxExtend.messageWarning(error.Message);
return;
}
}
load();
}
catch (Exception)
{
}
}
}
public bool checkBillIsExist(string billNumber)
{
bool isRet = false;
foreach (KeyValuePair <int, MaterielOutOtherOrderDetailsTable> index in m_tableDataList)
{
MaterielOutOtherOrderDetailsTable record = new MaterielOutOtherOrderDetailsTable();
if (index.Value.billNumber == billNumber)
{
isRet = true;
break;
}
}
return(isRet);
}
public SortedDictionary <int, MaterielOutOtherOrderDetailsTable> getMaterielOutOtherOrderCountInfo(string startDate, string endDate, bool isAllBill = false)
{
SortedDictionary <int, MaterielOutOtherOrderDetailsTable> dataList = new SortedDictionary <int, MaterielOutOtherOrderDetailsTable>();
string querySQL = "SELECT A1.[PKEY],A1.[ROW_NUMBER],A1.[MATERIEL_ID], A1.[BILL_NUMBER],A1.[PRICE],A1.[VALUE],A1.[NOTE] ";
querySQL += "FROM [WAREHOUSE_MANAGEMENT_OUT_OTHER_DETAILS] A1, WAREHOUSE_MANAGEMENT_OUT_OTHER A2";
querySQL += " WHERE A1.BILL_NUMBER = A2.BILL_NUMBER";
querySQL += " AND A2.TRADING_DATE >= '" + startDate + "' AND A2.TRADING_DATE <= '" + endDate + "' AND IS_RED_BILL = 0 ";
if (!isAllBill)
{
querySQL += " AND A2.IS_REVIEW = 1";
}
querySQL += " ORDER BY A1.[PKEY]";
using (DataTable dataTable = DatabaseAccessFactoryInstance.Instance.QueryDataTable(FormMain.DB_NAME, querySQL))
{
foreach (DataRow row in dataTable.Rows)
{
MaterielOutOtherOrderDetailsTable record = new MaterielOutOtherOrderDetailsTable();
record.pkey = DbDataConvert.ToInt32(row["PKEY"]);
record.rowNumber = DbDataConvert.ToString(row["ROW_NUMBER"]);
record.billNumber = DbDataConvert.ToString(row["BILL_NUMBER"]);
record.materielID = DbDataConvert.ToInt32(row["MATERIEL_ID"]);
// 根据物料ID得到物料相关信息
MaterielTable materiel = Materiel.getInctance().getMaterielInfoFromPkey(record.materielID);
record.materielName = materiel.name;
record.materielModel = materiel.model;
record.materielUnitSale = AuxiliaryMaterial.getInctance().getAuxiliaryMaterialNameFromPkey("BASE_UNIT_LIST", materiel.unitSale);
record.price = DbDataConvert.ToDouble(row["PRICE"]);
record.value = DbDataConvert.ToDouble(row["VALUE"]);
record.sumMoney = record.price * record.value;
record.billNumber = DbDataConvert.ToString(row["NOTE"]);
dataList.Add(dataList.Count, record);
}
}
return(dataList);
}
private void load()
{
m_tableDataList.Clear();
string querySQL = "SELECT A1.[PKEY],A1.[ROW_NUMBER],A1.[MATERIEL_ID], A1.[BILL_NUMBER],A1.[PRICE],A1.[VALUE],A1.[NOTE] ";
querySQL += " FROM [WAREHOUSE_MANAGEMENT_OUT_OTHER_DETAILS] A1, WAREHOUSE_MANAGEMENT_OUT_OTHER A2";
querySQL += " WHERE A1.BILL_NUMBER = A2.BILL_NUMBER";
querySQL += " ORDER BY A1.[PKEY]";
using (DataTable dataTable = DatabaseAccessFactoryInstance.Instance.QueryDataTable(FormMain.DB_NAME, querySQL))
{
foreach (DataRow row in dataTable.Rows)
{
MaterielOutOtherOrderDetailsTable record = new MaterielOutOtherOrderDetailsTable();
record.pkey = DbDataConvert.ToInt32(row["PKEY"]);
record.rowNumber = DbDataConvert.ToString(row["ROW_NUMBER"]);
record.billNumber = DbDataConvert.ToString(row["BILL_NUMBER"]);
record.materielID = DbDataConvert.ToInt32(row["MATERIEL_ID"]);
// 根据物料ID得到物料相关信息
MaterielTable materiel = Materiel.getInctance().getMaterielInfoFromPkey(record.materielID);
record.materielName = materiel.name;
record.materielModel = materiel.model;
record.materielUnitSale = AuxiliaryMaterial.getInctance().getAuxiliaryMaterialNameFromPkey("BASE_UNIT_LIST", materiel.unitSale);
record.price = DbDataConvert.ToDouble(row["PRICE"]);
record.value = DbDataConvert.ToDouble(row["VALUE"]);
record.sumMoney = record.price * record.value;
record.note = DbDataConvert.ToString(row["NOTE"]);
m_tableDataList.Add(m_tableDataList.Count, record);
}
}
}
private bool updateMaterielData(string billNumber)
{
bool isRet = true;
SortedDictionary <int, MaterielOutOtherOrderDetailsTable> dataList =
MaterielOutOtherOrderDetails.getInctance().getMaterielOutOtherInfoFromBillNumber(billNumber);
if (!m_isRedBill)
{
// 首先检查物料库存余额是否大于本次出库单交易额，如果小于，则提示用户，但是审核失败
foreach (KeyValuePair <int, MaterielOutOtherOrderDetailsTable> index in dataList)
{
MaterielOutOtherOrderDetailsTable record = index.Value;
InitMaterielTable materielRecord = new InitMaterielTable();
materielRecord.materielID = record.materielID;
materielRecord.value = record.value;
InitMaterielTable materielStorageData = InitMateriel.getInctance().getMaterielInfoFromMaterielID(record.materielID);
if (materielStorageData.value < materielRecord.value)
{
isRet = false;
MessageBoxExtend.messageWarning("物料：[" + materielRecord.materielID + "]库存数量小于当前交易数量,单据审核失败");
break;
}
}
}
if (isRet)
{
foreach (KeyValuePair <int, MaterielOutOtherOrderDetailsTable> index in dataList)
{
MaterielOutOtherOrderDetailsTable record = index.Value;
//InitMaterielTable materielRecord = new InitMaterielTable();
//materielRecord.materielID = record.materielID;
//materielRecord.value = (int)record.value;
//// 交易后库存数量等于当前仓库库存数减去本次交易数
//InitMaterielTable materielStorageData = InitMateriel.getInctance().getMaterielInfoFromMaterielID(record.materielID);
//if (m_isRedBill)
//{
// // 如果是销售退货，库存数量应该增加
// materielRecord.value = materielStorageData.value + materielRecord.value;
//}
//else
//{
// materielRecord.value = materielStorageData.value - materielRecord.value;
//}
//InitMateriel.getInctance().materielOutStorage(materielRecord.materielID, materielRecord.value);
#region 更新库存汇总表(INIT_STORAGE_STOCK)
// 交易完毕后库存数量
double tradingStocksValue = record.value;
// 交易后库存数量等于当前仓库库存数减去本次交易数
InitMaterielTable materielStorageData = InitMateriel.getInctance().getMaterielInfoFromMaterielID(record.materielID);
if (m_isRedBill)
{
// 如果是销售退货，库存数量应该增加
tradingStocksValue = materielStorageData.value + tradingStocksValue;
}
else
{
tradingStocksValue = materielStorageData.value - tradingStocksValue;
}
InitMateriel.getInctance().materielOutStorage(record.materielID, tradingStocksValue);
#endregion
#region 更新存货明细账表(STORAGE_STOCK_DETAIL) 2012-1-16 00:56
StorageStockDetailTable storageStockDetailRecord = new StorageStockDetailTable();
storageStockDetailRecord.materielID = record.materielID;
storageStockDetailRecord.tradingDate = DateTime.Now.ToString("yyyyMMdd");
storageStockDetailRecord.billNumber = billNumber;
storageStockDetailRecord.thingsType = "其他出库";
storageStockDetailRecord.isIn = 0;
// 本次交易数量和单价
if (m_isRedBill)
{
storageStockDetailRecord.value = record.value * -1;
}
else
{
storageStockDetailRecord.value = record.value;
}
storageStockDetailRecord.price = record.price;
// 交易完毕后数量和单价
storageStockDetailRecord.storageValue = tradingStocksValue;
storageStockDetailRecord.storagePrice = materielStorageData.price;
StorageStockDetail.getInctance().insert(storageStockDetailRecord);
#endregion
}
}
return(isRet);
}