private void load()
{
string sql = "SELECT [PKEY],[MATERIEL_ID],[VALUE] ,[PRICE] FROM [dbo].[INIT_STORAGE_STOCK] ORDER BY [MATERIEL_ID]";
m_materielStockList.Clear();
using (DataTable dataTable = DatabaseAccessFactoryInstance.Instance.QueryDataTable(FormMain.DB_NAME, sql))
{
foreach (DataRow row in dataTable.Rows)
{
InitMaterielTable record = new InitMaterielTable();
record.pkey = DbDataConvert.ToInt32(row[0]);
record.materielID = DbDataConvert.ToInt32(row[1]);
record.value = DbDataConvert.ToDouble(row[2]);
record.price = DbDataConvert.ToDouble(row[3]);
// 根据物料ID，得到物料信息
MaterielTable materielInfo = Materiel.getInctance().getMaterielInfoFromPkey(record.materielID);
record.name = materielInfo.name;
record.storage = AuxiliaryMaterial.getInctance().getAuxiliaryMaterialNameFromPkey("BASE_STORAGE_LIST", materielInfo.storage);
record.unitPurchase = AuxiliaryMaterial.getInctance().getAuxiliaryMaterialNameFromPkey("BASE_UNIT_LIST", materielInfo.unit);
m_materielStockList.Add(m_materielStockList.Count, record);
}
}
}
public void update(int pkey, InitMaterielTable record, bool isDisplayMessageBox = true)
{
string update = "UPDATE [dbo].[INIT_STORAGE_STOCK] SET ";
update += "[VALUE] = " + record.value + ",";
update += "[PRICE] = " + record.price;
update += " WHERE PKEY = " + Convert.ToString(pkey);
try
{
DatabaseAccessFactoryInstance.Instance.ExecuteCommand(FormMain.DB_NAME, update);
if (isDisplayMessageBox)
{
MessageBoxExtend.messageOK("数据修改成功");
}
load();
}
catch (Exception error)
{
MessageBoxExtend.messageWarning(error.Message);
return;
}
}
public void materielOutStorage(InitMaterielTable record, bool isDisplayMessageBox = true)
{
double price = 0;
double materielNum = 0;
price = record.price;
materielNum = record.value;
InitMaterielTable storageExistMaterielRecord = getMaterielInfoFromMaterielID(record.materielID);
// 根据id，得到物料的详细信息,进一步得到物料的计价方式
MaterielTable materielInfo = Materiel.getInctance().getMaterielInfoFromPkey(storageExistMaterielRecord.materielID);
int valuation = materielInfo.valuation;
string valuationName = AuxiliaryMaterial.getInctance().getAuxiliaryMaterialNameFromPkey("BASE_VALUATION_TYPE_LIST", valuation);
if (valuation == 0 || valuationName.IndexOf("加权") != -1)
{
double newPrice = MobileWeightedUnitPrice.calculateMaterielNewPriceOutStorage(
storageExistMaterielRecord.price,
storageExistMaterielRecord.value,
price, materielNum);
InitMaterielTable newRecord = new InitMaterielTable();
newRecord.pkey = storageExistMaterielRecord.pkey;
newRecord.materielID = storageExistMaterielRecord.materielID;
newRecord.price = newPrice;
newRecord.value = storageExistMaterielRecord.value - materielNum;
update(newRecord.pkey, newRecord, isDisplayMessageBox);
return;
}
}
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
}
}
// xx阶段xx产品，销售成本，毛利润明细信息(存货核算->销售成本明细和销售毛利润明细报表用)
public SortedDictionary <int, SaleProfitCounInfoStruct> getSaleOutOrderDetailsInfo(string startDate, string endDate, bool isAllBill = false)
{
SortedDictionary <int, SaleProfitCounInfoStruct> dataList = new SortedDictionary <int, SaleProfitCounInfoStruct>();
string querySQL = "SELECT [PKEY], [MATERIEL_ID], [BILL_NUMBER], [PRICE], [VALUE], [PRICE]*[VALUE]+[TRANSPORTATION_COST]+[OTHER_COST] AS TOTAL_SALE_MONEY ";
querySQL += "FROM [SALE_OUT_ORDER_DETAILS] WHERE BILL_NUMBER IN(";
querySQL += "SELECT BILL_NUMBER FROM [dbo].[SALE_OUT_ORDER] WHERE ";
querySQL += "TRADING_DATE >= '" + startDate + "' AND TRADING_DATE <= '" + endDate + "' AND IS_RED_BILL = 0 ";
if (!isAllBill)
{
querySQL += " AND IS_REVIEW = 1";
}
querySQL += ") ORDER BY PKEY";
using (DataTable dataTable = DatabaseAccessFactoryInstance.Instance.QueryDataTable(FormMain.DB_NAME, querySQL))
{
foreach (DataRow row in dataTable.Rows)
{
SaleProfitCounInfoStruct record = new SaleProfitCounInfoStruct();
record.materielID = DbDataConvert.ToInt32(row["MATERIEL_ID"]);
record.billNumber = DbDataConvert.ToString(row["BILL_NUMBER"]);
MaterielTable materiel = Materiel.getInctance().getMaterielInfoFromPkey(record.materielID);
record.materielName = materiel.name;
record.materielUnitSale = AuxiliaryMaterial.getInctance().getAuxiliaryMaterialNameFromPkey("BASE_UNIT_LIST", materiel.unitSale);
record.priceSale = DbDataConvert.ToDouble(row["PRICE"]);
record.value = DbDataConvert.ToDouble(row["VALUE"]);
record.saleSum = DbDataConvert.ToDouble(row["TOTAL_SALE_MONEY"]);
// 成本价 = 交易数量 * 库存加权单价
InitMaterielTable temp = InitMateriel.getInctance().getMaterielInfoFromMaterielID(record.materielID);
record.priceCost = temp.price;
record.costSum = record.value * record.priceCost;
// 总毛利润
record.profitSum = record.saleSum - record.costSum;
dataList.Add(dataList.Count, record);
}
}
return(dataList);
}
public bool checkMaterielIsExist(int materielID)
{
bool isRet = false;
foreach (KeyValuePair <int, InitMaterielTable> index in m_materielStockList)
{
InitMaterielTable record = new InitMaterielTable();
record = index.Value;
if (record.materielID == materielID)
{
isRet = true;
break;
}
}
return(isRet);
}
public InitMaterielTable getMaterielInfoFromMaterielID(int materielID)
{
InitMaterielTable materielRecord = new InitMaterielTable();
foreach (KeyValuePair <int, InitMaterielTable> index in m_materielStockList)
{
InitMaterielTable record = new InitMaterielTable();
record = index.Value;
if (record.materielID == materielID)
{
materielRecord = record;
break;
}
}
return(materielRecord);
}
public void fileImport(string filePath)
{
int rowIndex = 0;
try
{
// 正式导入数据前，首先情况INIT_STORAGE_STOCK表数据
DataTable dataTable = DatabaseAccessFactoryInstance.Instance.QueryDataTable(FormMain.DB_NAME, "DELETE FROM [dbo].[INIT_STORAGE_STOCK]");
string sheetName = "存货初始数据";
if (ExcelDocProc.getInstance().openFile(filePath))
{
string materielID, value, price;
for (rowIndex = 0;; rowIndex++)
{
materielID = ExcelDocProc.getInstance().getGridValue(sheetName, rowIndex, 0);
price = ExcelDocProc.getInstance().getGridValue(sheetName, rowIndex, 1);
value = ExcelDocProc.getInstance().getGridValue(sheetName, rowIndex, 2);
if (materielID.Length == 0)
{
break;
}
else
{
InitMaterielTable record = new InitMaterielTable();
record.materielID = Convert.ToInt32(materielID.ToString());
record.value = Convert.ToInt32(value.ToString());
record.price = Convert.ToDouble(price.ToString());
insert(record, false);
}
}
MessageBoxExtend.messageOK("存货初始数据导入成功");
}
}
catch (Exception)
{
MessageBoxExtend.messageWarning("文件导入失败，[" + Convert.ToString(rowIndex) + "]行数据有误，请仔细核对");
return;
}
}
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
}
}
public void insert(InitMaterielTable record, bool isDisplayMessageBox = true)
{
double price = 0;
double materielNum = 0;
string insert = "INSERT INTO [dbo].[INIT_STORAGE_STOCK]([MATERIEL_ID],[VALUE],[PRICE]) VALUES (";
price = record.price;
materielNum = record.value;
/* 插入逻辑
* 1、检查库存表是否已经存在该物料的库存信息
* 2、得到物料的计价方式，后进先出或是先进后出或是移动加权平均
* 3、如果是移动加权平均，计算移动加权单价
* 4、如果是后进先出或是先进后出计价方式，直接插入到数据库
* */
if (checkMaterielIsExist(record.materielID))
{
InitMaterielTable storageExistMaterielRecord = getMaterielInfoFromMaterielID(record.materielID);
// 根据id，得到物料的详细信息,进一步得到物料的计价方式
MaterielTable materielInfo = Materiel.getInctance().getMaterielInfoFromPkey(storageExistMaterielRecord.materielID);
int valuation = materielInfo.valuation;
string valuationName = AuxiliaryMaterial.getInctance().getAuxiliaryMaterialNameFromPkey("BASE_VALUATION_TYPE_LIST", valuation);
if (valuation == 0 || valuationName.IndexOf("加权") != -1)
{
double newPrice = MobileWeightedUnitPrice.calculateMaterielNewPrice(
storageExistMaterielRecord.price,
storageExistMaterielRecord.value,
price, materielNum);
InitMaterielTable newRecord = new InitMaterielTable();
newRecord.pkey = storageExistMaterielRecord.pkey;
newRecord.materielID = storageExistMaterielRecord.materielID;
newRecord.price = newPrice;
newRecord.value = materielNum + storageExistMaterielRecord.value;
update(newRecord.pkey, newRecord, isDisplayMessageBox);
return;
}
}
// 如果不是加权平均或仓库中还不存在该物料信息，直接插入本条记录
insert += record.materielID + ",";
insert += materielNum + ",";
insert += "'" + Convert.ToString(price) + "'";
insert += ")";
try
{
DatabaseAccessFactoryInstance.Instance.ExecuteCommand(FormMain.DB_NAME, insert);
if (isDisplayMessageBox)
{
MessageBoxExtend.messageOK("数据保存成功");
}
load();
}
catch (Exception error)
{
MessageBoxExtend.messageWarning(error.Message);
return;
}
}
