private void load()
        {
            m_tableDataList.Clear();

            string querySQL = "SELECT [PKEY],[ROW_NUMBER],[MATERIEL_ID], [BILL_NUMBER],[PRICE],[VALUE],[NOTE], [IS_CANCEL] FROM [WAREHOUSE_MANAGEMENT_PRO_OCCUPIED_DETAILS] ORDER BY [PKEY] DESC";

            using (DataTable dataTable = DatabaseAccessFactoryInstance.Instance.QueryDataTable(FormMain.DB_NAME, querySQL))
            {
                foreach (DataRow row in dataTable.Rows)
                {
                    MaterielProOccupiedOrderDetailsTable record = new MaterielProOccupiedOrderDetailsTable();

                    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"]);
                    record.isCancel = DbDataConvert.ToString(row["IS_CANCEL"]);

                    m_tableDataList.Add(m_tableDataList.Count, record);
                }
            }
        }
Exemplo n.º 2
0
        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);
                }
            }
        }
Exemplo n.º 3
0
        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;
            }
        }
Exemplo n.º 4
0
        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);
                }
            }
        }
Exemplo n.º 5
0
        static public Materiel getInctance()
        {
            if (m_instance == null)
            {
                m_instance = new Materiel();
            }

            return(m_instance);
        }
Exemplo n.º 6
0
        // 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);
        }
Exemplo n.º 7
0
        public SortedDictionary <int, MaterielInOrderDetailsTable> getMaterielInOrderCountInfo(string startDate, string endDate, bool isAllBill = false)
        {
            SortedDictionary <int, MaterielInOrderDetailsTable> dataList = new SortedDictionary <int, MaterielInOrderDetailsTable>();
            string querySQL = "SELECT A1.[PKEY],A1.[ROW_NUMBER],A1.[MATERIEL_ID], A1.[BILL_NUMBER],A1.[PRICE],A1.[VALUE],A1.[NOTE] ";

            querySQL += "FROM [WAREHOUSE_MANAGEMENT_IN_DETAILS] A1, WAREHOUSE_MANAGEMENT_IN 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)
                {
                    MaterielInOrderDetailsTable record = new MaterielInOrderDetailsTable();

                    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);
        }
Exemplo n.º 8
0
        private void load()
        {
            string sql = "SELECT [PKEY],[ROW_NUMBER],[MATERIEL_ID],[BILL_NUMBER],[PRICE],[VALUE]";

            sql += ",[TRANSPORTATION_COST],[OTHER_COST] FROM [dbo].[PURCHASE_IN_ORDER_DETAILS] ORDER BY PKEY";

            m_tableDataList.Clear();

            using (DataTable dataTable = DatabaseAccessFactoryInstance.Instance.QueryDataTable(FormMain.DB_NAME, sql))
            {
                foreach (DataRow row in dataTable.Rows)
                {
                    PurchaseInOrderDetailsTable record = new PurchaseInOrderDetailsTable();

                    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.brand                = materiel.brand;
                    record.materielUnitPurchase = AuxiliaryMaterial.getInctance().getAuxiliaryMaterialNameFromPkey("BASE_UNIT_LIST", materiel.unitPurchase);

                    record.price                = DbDataConvert.ToDouble(row["PRICE"]);
                    record.value                = DbDataConvert.ToDouble(row["VALUE"]);
                    record.sumMoney             = record.price * record.value;
                    record.costApportionments   = DbDataConvert.ToDouble(row["TRANSPORTATION_COST"]);
                    record.noCostApportionments = DbDataConvert.ToDouble(row["OTHER_COST"]);
                    record.totalMoney           = record.sumMoney + record.costApportionments + record.noCostApportionments;

                    m_tableDataList.Add(m_tableDataList.Count, record);
                }
            }
        }
Exemplo n.º 9
0
        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_EARNINGS_DETAILS] A1, WAREHOUSE_MANAGEMENT_OUT_EARNINGS 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)
                {
                    MaterielOutEarningsOrderDetailsTable record = new MaterielOutEarningsOrderDetailsTable();

                    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);
                }
            }
        }
Exemplo n.º 10
0
        // xx阶段xx产品领料金额统计
        public SortedDictionary <int, MaterielOutOrderDetailsTable> getMaterielOutOrderCountInfo2(string startDate, string endDate, bool isAllBill = false)
        {
            SortedDictionary <int, MaterielOutOrderDetailsTable> dataList = new SortedDictionary <int, MaterielOutOrderDetailsTable>();
            string querySQL = "SELECT [MATERIEL_ID], SUM([PRICE]*[VALUE]) AS TOTAL_MONEY ";

            querySQL += "FROM [WAREHOUSE_MANAGEMENT_OUT_DETAILS] WHERE BILL_NUMBER IN(";

            querySQL += "SELECT BILL_NUMBER FROM [dbo].[WAREHOUSE_MANAGEMENT_OUT] WHERE ";
            querySQL += "TRADING_DATE >= '" + startDate + "' AND TRADING_DATE <= '" + endDate + "' AND IS_RED_BILL = 0 ";

            if (!isAllBill)
            {
                querySQL += " AND IS_REVIEW = 1";
            }

            querySQL += ") GROUP BY [MATERIEL_ID]";
            querySQL += " ORDER BY TOTAL_MONEY DESC";

            using (DataTable dataTable = DatabaseAccessFactoryInstance.Instance.QueryDataTable(FormMain.DB_NAME, querySQL))
            {
                foreach (DataRow row in dataTable.Rows)
                {
                    MaterielOutOrderDetailsTable record = new MaterielOutOrderDetailsTable();

                    record.materielID = DbDataConvert.ToInt32(row["MATERIEL_ID"]);

                    MaterielTable materiel = Materiel.getInctance().getMaterielInfoFromPkey(record.materielID);
                    record.materielName  = materiel.name;
                    record.materielModel = materiel.model;

                    record.sumMoney = DbDataConvert.ToDouble(row["TOTAL_MONEY"]);

                    dataList.Add(dataList.Count, record);
                }
            }

            return(dataList);
        }
Exemplo n.º 11
0
        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;
            }
        }