private void load(string sql)
{
m_tableDataList.Clear();
using (DataTable dataTable = DatabaseAccessFactoryInstance.Instance.QueryDataTable(FormMain.DB_NAME, sql))
{
foreach (DataRow row in dataTable.Rows)
{
StorageStockDetailTable record = new StorageStockDetailTable();
record.pkey = DbDataConvert.ToInt32(row["PKEY"]);
record.materielID = DbDataConvert.ToInt32(row["MATERIEL_ID"]);
MaterielTable materiel = Materiel.getInctance().getMaterielInfoFromPkey(record.materielID);
record.materielName = materiel.name;
record.model = materiel.model;
record.tradingDate = DbDataConvert.ToDateTime(row["TRADING_DATE"]).ToString("yyyy-MM-dd");
record.billNumber = DbDataConvert.ToString(row["BILL_NUMBER"]);
record.thingsType = DbDataConvert.ToString(row["THINGS_TYPE"]);
record.isIn = DbDataConvert.ToInt32(row["IS_IN"]);
record.value = DbDataConvert.ToDouble(row["VALUE"]);
record.price = DbDataConvert.ToDouble(row["PRICE"]);
record.sumMoney = record.value * record.price;
record.storageValue = DbDataConvert.ToDouble(row["STORAGE_VALUE"]);
record.storagePrice = DbDataConvert.ToDouble(row["STORAGE_PRICE"]);
record.storageMoney = record.storageValue * record.storagePrice;
m_tableDataList.Add(m_tableDataList.Count, record);
}
}
}
public void insert(StorageStockDetailTable record)
{
string insert = "INSERT INTO [dbo].[STORAGE_STOCK_DETAIL]([MATERIEL_ID],[TRADING_DATE],[BILL_NUMBER],[THINGS_TYPE]";
insert += ",[IS_IN],[VALUE],[PRICE],[STORAGE_VALUE],[STORAGE_PRICE]) VALUES(";
insert += record.materielID + ",";
insert += "'" + record.tradingDate + "',";
insert += "'" + record.billNumber + "',";
insert += "'" + record.thingsType + "',";
insert += record.isIn + ",";
insert += record.value + ",";
insert += record.price + ",";
insert += record.storageValue + ",";
insert += record.storagePrice;
insert += ")";
try
{
DatabaseAccessFactoryInstance.Instance.ExecuteCommand(FormMain.DB_NAME, insert);
}
catch (Exception error)
{
MessageBoxExtend.messageWarning(error.Message);
return;
}
}
// 得到指定日期之前的最后一条记录的结存相关信息。被用于存货核算界面存货明细账界面，得到xx物料期初余额
public StorageStockDetailTable getMaterielStorageStockDetailInfo(int materielID, string startDate)
{
StorageStockDetailTable record = new StorageStockDetailTable();
string sql = "SELECT [PKEY],[MATERIEL_ID],[TRADING_DATE],[BILL_NUMBER],[THINGS_TYPE],[IS_IN],[VALUE],[PRICE],";
sql += "[STORAGE_VALUE],[STORAGE_PRICE] FROM [dbo].[STORAGE_STOCK_DETAIL] WHERE MATERIEL_ID = " + materielID;
sql += " AND TRADING_DATE < '" + startDate + "' ORDER BY PKEY DESC";
load(sql);
if (m_tableDataList.Count > 0)
{
record = m_tableDataList[0];
}
//else
//{
// // 此处需要修改代码，当在STORAGE_STOCK_DETAIL没查相应物料的记录时，需要根据物料ID，去库存表中查到相应的记录
// InitMaterielTable data = InitMateriel.getInctance().getMaterielInfoFromPkey(materielID);
// record.storageValue = data.value;
// record.storagePrice = data.price;
// record.storageMoney = data.value * data.price;
//}
return(record);
}
public SortedDictionary <int, StorageStockDetailTable> getMaterielStorageStockDetailHistoryInfo(string endDate)
{
SortedDictionary <int, StorageStockDetailTable> tableDataList = new SortedDictionary <int, StorageStockDetailTable>();
string sql = "SELECT [PKEY],[MATERIEL_ID],[TRADING_DATE],[STORAGE_VALUE],[STORAGE_PRICE] FROM [dbo].[STORAGE_STOCK_DETAIL] WHERE TRADING_DATE <= '";
sql += endDate + "' ORDER BY PKEY DESC";
using (DataTable dataTable = DatabaseAccessFactoryInstance.Instance.QueryDataTable(FormMain.DB_NAME, sql))
{
foreach (DataRow row in dataTable.Rows)
{
StorageStockDetailTable record = new StorageStockDetailTable();
record.pkey = DbDataConvert.ToInt32(row["PKEY"]);
record.materielID = DbDataConvert.ToInt32(row["MATERIEL_ID"]);
record.tradingDate = DbDataConvert.ToDateTime(row["TRADING_DATE"]).ToString("yyyy-MM-dd");
record.storageValue = DbDataConvert.ToDouble(row["STORAGE_VALUE"]);
record.storagePrice = DbDataConvert.ToDouble(row["STORAGE_PRICE"]);
tableDataList.Add(tableDataList.Count, record);
}
}
return(tableDataList);
}
private void updateMaterielData(string billNumber)
{
SortedDictionary <int, PurchaseInOrderDetailsTable> dataList =
PurchaseInOrderDetails.getInctance().getPurchaseInfoFromBillNumber(billNumber);
foreach (KeyValuePair <int, PurchaseInOrderDetailsTable> index in dataList)
{
PurchaseInOrderDetailsTable record = index.Value;
#region 更新库存汇总表(INIT_STORAGE_STOCK)
InitMaterielTable materielRecord = new InitMaterielTable();
materielRecord.materielID = record.materielID;
materielRecord.value = record.value;
materielRecord.price = record.price;
if (record.sumMoney != record.totalMoney)
{
// 如果物料总费用和总金额不相等，代表有需要计入成本的费用发生，需要重新核算物料单价
materielRecord.price = (record.costApportionments + record.sumMoney) / record.value;
}
if (m_isRedBill)
{
InitMateriel.getInctance().materielOutStorage(materielRecord, false);
}
else
{
InitMateriel.getInctance().insert(materielRecord, false);
}
#endregion
#region 更新存货明细账表(STORAGE_STOCK_DETAIL) 2012-1-16 01:22
StorageStockDetailTable storageStockDetailRecord = new StorageStockDetailTable();
storageStockDetailRecord.materielID = record.materielID;
storageStockDetailRecord.tradingDate = DateTime.Now.ToString("yyyyMMdd");
storageStockDetailRecord.billNumber = billNumber;
storageStockDetailRecord.thingsType = "采购入库";
storageStockDetailRecord.isIn = 1;
// 本次交易数量和单价
if (m_isRedBill)
{
storageStockDetailRecord.value = record.value * -1;
}
else
{
storageStockDetailRecord.value = record.value;
}
storageStockDetailRecord.price = record.price;
// 交易完毕后数量和单价
InitMaterielTable materielStorageData = InitMateriel.getInctance().getMaterielInfoFromMaterielID(record.materielID);
storageStockDetailRecord.storageValue = materielStorageData.value;
storageStockDetailRecord.storagePrice = materielStorageData.price;
StorageStockDetail.getInctance().insert(storageStockDetailRecord);
#endregion
}
}
private bool updateMaterielData(string billNumber)
{
bool isRet = true;
SortedDictionary <int, SaleOutOrderDetailsTable> dataList =
SaleOutOrderDetails.getInctance().getSaleOutInfoFromBillNumber(billNumber);
if (!m_isRedBill)
{
// 首先检查物料库存余额是否大于本次出库单交易额，如果小于，则提示用户，但是审核失败
foreach (KeyValuePair <int, SaleOutOrderDetailsTable> index in dataList)
{
SaleOutOrderDetailsTable 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, SaleOutOrderDetailsTable> index in dataList)
{
SaleOutOrderDetailsTable record = index.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);
}
private void updateMaterielData(string billNumber)
{
SortedDictionary <int, MaterielInEarningsOrderDetailsTable> dataList =
MaterielInEarningsOrderDetails.getInctance().getMaterielInEarningsInfoFromBillNumber(billNumber);
foreach (KeyValuePair <int, MaterielInEarningsOrderDetailsTable> index in dataList)
{
MaterielInEarningsOrderDetailsTable record = index.Value;
//InitMaterielTable materielRecord = new InitMaterielTable();
//materielRecord.materielID = record.materielID;
//materielRecord.value = (int)record.value;
//materielRecord.price = record.price;
//if (m_isRedBill)
//{
// InitMateriel.getInctance().materielOutStorage(materielRecord, false);
//}
//else
//{
// InitMateriel.getInctance().insert(materielRecord, false);
//}
#region 更新库存汇总表(INIT_STORAGE_STOCK)
InitMaterielTable materielRecord = new InitMaterielTable();
materielRecord.materielID = record.materielID;
materielRecord.value = record.value;
materielRecord.price = record.price;
if (m_isRedBill)
{
InitMateriel.getInctance().materielOutStorage(materielRecord, false);
}
else
{
InitMateriel.getInctance().insert(materielRecord, false);
}
#endregion
#region 更新存货明细账表(STORAGE_STOCK_DETAIL) 2012-1-16 01:22
StorageStockDetailTable storageStockDetailRecord = new StorageStockDetailTable();
storageStockDetailRecord.materielID = record.materielID;
storageStockDetailRecord.tradingDate = DateTime.Now.ToString("yyyyMMdd");
storageStockDetailRecord.billNumber = billNumber;
storageStockDetailRecord.thingsType = "盘盈入库";
storageStockDetailRecord.isIn = 1;
// 本次交易数量和单价
if (m_isRedBill)
{
storageStockDetailRecord.value = record.value * -1;
}
else
{
storageStockDetailRecord.value = record.value;
}
storageStockDetailRecord.price = record.price;
// 交易完毕后数量和单价
InitMaterielTable materielStorageData = InitMateriel.getInctance().getMaterielInfoFromMaterielID(record.materielID);
storageStockDetailRecord.storageValue = materielStorageData.value;
storageStockDetailRecord.storagePrice = materielStorageData.price;
StorageStockDetail.getInctance().insert(storageStockDetailRecord);
#endregion
}
}