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; } }