예제 #1
0
        private void ProcessStockInDetail(DepartmentStockInDetail detail, DataAccessLayer dal)
        {
            // insert type name
            string typeName = detail.Product.ProductMaster.ProductType.TypeName;
            object id = dal.GetSingleValue("Select type_id from product_type where type_name = '" + typeName + "'");
            if (id == null || id.ToString() == string.Empty)
            {
                id = dal.GetSingleValue("Select max(type_id) from product_type ");
                if (id == null || id.ToString() == string.Empty)
                {
                    id = 1;
                }
                else
                {
                    id = Convert.ToInt32(id.ToString()) + 1;
                }
                dal.ExecuteQuery("insert into product_type(type_id, type_name) values (" + id.ToString() + ", N'" + typeName.Replace("'", "''") + "')");
            }

            // insert color name
            string colorName = detail.Product.ProductMaster.ProductColor.ColorName;
            id = dal.GetSingleValue("Select color_id from product_color where color_name = '" + colorName.Replace("'", "''") + "'");
            if (id == null || id.ToString() == string.Empty)
            {

                id = dal.GetSingleValue("Select max(color_id) from product_color ");
                if (id == null || id.ToString() == string.Empty)
                {
                    id = 1;
                }
                else
                {
                    id = Convert.ToInt32(id.ToString()) + 1;
                }
                dal.ExecuteQuery("insert into product_color(Color_id, Color_name) values (" + id.ToString() + ", N'" + colorName.Replace("'", "''") + "')");
            }

            // insert size name
            string sizeName = detail.Product.ProductMaster.ProductSize.SizeName;
            id = dal.GetSingleValue("Select size_id from product_size where size_name = '" + sizeName.Replace("'", "''") + "'");
            if (id == null || id.ToString() == string.Empty)
            {
                id = dal.GetSingleValue("Select max(size_id) from product_size ");
                if (id == null || id.ToString() == string.Empty)
                {
                    id = 1;
                }
                else
                {
                    id = Convert.ToInt32(id.ToString()) + 1;
                }
                dal.ExecuteQuery("insert into product_size(size_id, size_name) values (" + id.ToString() + ", N'" + sizeName.Replace("'", "''") + "')");

            }

            // insert product master
            string productMasterId = detail.Product.ProductMaster.ProductMasterId;
            id = dal.GetSingleValue("Select product_master_id from product_master where product_master_id = '" + productMasterId + "'");

            if (id == null || id.ToString() == string.Empty)
            {
                id = dal.GetSingleValue("Select max(product_master_id) from product_master ");
                if (id == null || id.ToString() == string.Empty)
                {
                    id = 1;
                }
                else
                {
                    id = Convert.ToInt32(id.ToString()) + 1;
                }
                dal.ExecuteQuery("insert into product_master(product_master_id, product_name, size_id, color_id, type_id, create_date,description) values ('"
                    + productMasterId
                    + "', '" + detail.Product.ProductMaster.ProductName.Replace("'", "''")
                    + "', " + detail.Product.ProductMaster.ProductSize.SizeId + ", "
                    + detail.Product.ProductMaster.ProductColor.ColorId + ", "
                    + detail.Product.ProductMaster.ProductType.TypeId + ", '"
                    + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "','"
                    + detail.Product.ProductMaster.Description + "')");

            }

            // product
            string productId = detail.Product.ProductId;
            id = dal.GetSingleValue("Select product_id from product where product_id = '" + productId + "'");
            if (id == null || id.ToString() == string.Empty)
            {
                dal.ExecuteQuery("insert into product(product_id, product_master_id, quantity, price, create_date) values ('"
                + productId
                + "', '" + productMasterId
                + "', " + detail.Quantity + ", " + detail.Price + ", '" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "')");
            }

            // dept-price
            id = dal.GetSingleValue("Select product_master_id from department_price where product_master_id = '" + productMasterId + "'");
            // chi cap nhat gia le, khong cap nhat gia ban si
            if (id == null || id.ToString() == string.Empty)
            {

                dal.ExecuteQuery("insert into department_price(department_id, product_master_id, price,  whole_sale_price) values ("
                    + "0, '" + productMasterId + "', " + detail.Price + ", " + 0 + ")");
            }
            else
            {
                dal.ExecuteQuery("update department_price set price = " + detail.Price
                    + " where  product_master_id = '" + productMasterId + "'");
            }
        }
예제 #2
0
        private bool DoStockIn(DataAccessLayer dal, Department department, DepartmentStockIn stockIn, bool NeedUpdateMasterData)
        {
            string deptStr = "";
            string extraZero = "";
            string startNum = "";
            if (department.DepartmentId > 999)
            {
                deptStr = department.DepartmentId.ToString();
                extraZero = "000";
                startNum = "001";
            }
            else
            {
                deptStr = string.Format("{0:000}", department.DepartmentId);
                extraZero = "00000";
                startNum = "00001";
            }

            string dateStr = DateTime.Now.ToString("yyMMdd");
            var selectMaxIdSQL = " select max(stock_in_id) from department_stock_in where stock_in_id > '" + dateStr + deptStr + extraZero + "'";

            ServerUtility.Log(logger, selectMaxIdSQL);
            var maxId = dal.GetSingleValue(selectMaxIdSQL);
            string stockInId = "";
            if(maxId == null || maxId.ToString() == string.Empty)
            {
                stockInId = dateStr + deptStr + startNum;
            }
            else
            {
                stockInId = string.Format("{0:00000000000000}", (Int64.Parse(maxId.ToString()) + 1));
            }

            string insertStockIn = " insert into department_stock_in(department_id,stock_in_id,stock_in_date,create_date,create_id,update_date,update_id,del_flg,exclusive_key) " +
                                   " values(" +
                                   department.DepartmentId + "," +
                                   "'" + stockInId + "'," +
                                   "'" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "'," +
                                   "'" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "'," +
                                   "'admin'," +
                                   "'" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "'," +
                                   "'admin'," +
                                   "0," +
                                   "1)";
            // insert department-stock-in
            ServerUtility.Log(logger, insertStockIn);
            dal.ExecuteQuery(insertStockIn);
            // insert department-stock-in-details
            foreach (DepartmentStockInDetail inDetail in stockIn.DepartmentStockInDetails)
            {
                if(NeedUpdateMasterData)
                {
                    ProcessStockInDetail(inDetail, dal);
                }

                // insert department-stock-in-detail
                string insertStockInDetail =
                    "insert into department_stock_in_detail(department_id,stock_in_id,product_id,product_master_id,quantity, price, del_flg,create_id,create_date,update_id,update_date) " +
                    " values(" +
                    department.DepartmentId + ",'" +
                    stockInId + "','" +
                    inDetail.Product.ProductId + "','" +
                    inDetail.Product.ProductMaster.ProductMasterId + "'," +
                    inDetail.Quantity + "," +
                    inDetail.Price + "," +
                    "0," +
                    "'admin'," +
                    "'" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "'," +
                    "'admin'," +
                    "'" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "')";

                ServerUtility.Log(logger, insertStockInDetail);
                dal.ExecuteQuery(insertStockInDetail);
                // insert department-stock
                //string insertStock = "insert into stock(stock_id,product_id,product_master_id,quantity,create_date,good_quantity,create_id,del_flg) values(18854,'001419317H01','0000000014193',1,'2009-07-27 00:00:00',1,'admin',0)";
                // stock
                string reqDeptStock = "Select product_id from department_stock where product_id ='" +
                                      inDetail.Product.ProductId + "'";
                ServerUtility.Log(logger, reqDeptStock);
                var id = dal.GetSingleValue(reqDeptStock);
                if (id == null || id.ToString() == string.Empty)
                {
                    string insertStock = "insert into department_stock(department_id, product_id, product_master_id, quantity, good_quantity, create_id,create_date,update_id,update_date) values ("
                                         + department.DepartmentId + ", '"
                                         + inDetail.Product.ProductId + "', '"
                                         + inDetail.Product.ProductMaster.ProductMasterId + "', "
                                         + inDetail.Quantity + ", "
                                         + inDetail.Quantity + ", "
                                         + "'admin'," + "'"
                                         + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "',"
                                         + "'admin'," + "'"
                                         + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "')";

                    ServerUtility.Log(logger, insertStock);
                    dal.ExecuteQuery(insertStock);
                }
                else
                {
                    string updateStock = " update department_stock "
                                         + " set quantity = quantity + " + inDetail.Quantity + " , "
                                         + " good_quantity = good_quantity + " + inDetail.Quantity + " "
                                         + " where product_id = '" + inDetail.Product.ProductId + "' "
                                         + " and department_id = " + department.DepartmentId;
                    ServerUtility.Log(logger, updateStock);
                    dal.ExecuteQuery(updateStock);
                }

            }
            return true;
        }
예제 #3
0
        private bool DoStockOut(DataAccessLayer dal, Department department,DepartmentStockOut stockOut,bool negativeStock)
        {
            // check stock before insert
            foreach (DepartmentStockOutDetail outDetail in stockOut.DepartmentStockOutDetails)
            {
                // stock
                var id = dal.GetSingleValue("Select product_id from department_stock where product_id ='" + outDetail.Product.ProductId+"'");
                if (id == null || id.ToString() == string.Empty)
                {
                    // ERROR
                    throw new BusinessException("Khong co ma hang " + outDetail.Product.ProductId + "trong kho cua hang !");
                }
                else
                {
                    if (!negativeStock)
                    {
                        var varQty =
                            dal.GetSingleValue("Select good_quantity from department_stock where product_id ='" +
                                               outDetail.Product.ProductId + "'");
                        long goodQty = 0;
                        Int64.TryParse(varQty.ToString(), out goodQty);
                        long remainGoodQty = goodQty - outDetail.Quantity;
                        if (remainGoodQty < 0)
                        {
                            throw new BusinessException(outDetail.Product.ProductId +
                                                        " không còn đủ số lượng trong kho.");
                        }
                    }
                }

            }
            // get max stock in id

            var selectMaxIdSQL = " select max(stock_out_id) from department_stock_out";
            var maxId = dal.GetSingleValue(selectMaxIdSQL);
            var stockOutId = (maxId == null || maxId.ToString() == string.Empty ) ? 1 : Int64.Parse(maxId.ToString()) + 1;

            var selectMaxDetIdSQL = " select max(stock_out_detail_id) from department_stock_out_detail";
            var maxDetId = dal.GetSingleValue(selectMaxDetIdSQL);
            var stockOutDetId = (maxDetId == null || maxDetId.ToString() == string.Empty) ? 1 : Int64.Parse(maxDetId.ToString()) + 1;

            string insertStockOut = " insert into department_stock_out(department_id," +
                                   "stock_out_id," +
                                   "stock_out_date," +
                                   "description," +
                                   "defect_status_id," +
                                   "confirm_flg," +
                                   "other_department_id," +
                                   "create_date," +
                                   "create_id," +
                                   "update_date," +
                                   "update_id," +
                                   "del_flg," +
                                   "exclusive_key) " +
                                   " values(" + // values
                                   department.DepartmentId + "," +
                                   "'" + stockOutId + "'," +
                                   "'" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "'," +
                                   "' Xuat hang ve kho phu ' " + "," +
                                   7 + "," +
                                   0 + "," +
                                   stockOut.DepartmentStockOutPK.DepartmentId + "," +
                                   "'" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "'," +
                                   "'admin'," +
                                   "'" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "'," +
                                   "'admin'," +
                                   "0," +
                                   "1)";
            // insert department-stock-in
            ServerUtility.Log(logger,insertStockOut);
            dal.ExecuteQuery(insertStockOut);
            // insert department-stock-in-details
            foreach (DepartmentStockOutDetail outDetail in stockOut.DepartmentStockOutDetails)
            {
                //ProcessStockInDetail(inDetail, dalSalePoint);

                // insert department-stock-in-detail
                string insertStockOutDetail =
                    "insert into department_stock_out_detail(stock_out_detail_id," +
                    "department_id," +
                    "stock_out_id," +
                    "product_id," +
                    "product_master_id," +
                    "quantity," +
                    " good_quantity," +
                    " del_flg," +
                    "create_id," +
                    "create_date," +
                    "update_id," +
                    "update_date) " +
                    " values(" + // values
                    stockOutDetId + "," +
                    department.DepartmentId + "," +
                    stockOutId + ",'" +
                    outDetail.Product.ProductId + "','" +
                    outDetail.Product.ProductMaster.ProductMasterId + "'," +
                    outDetail.Quantity + "," +
                    outDetail.Quantity + "," +
                    "0," +
                    "'admin'," +
                    "'" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "'," +
                    "'admin'," +
                    "'" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "')";
                ServerUtility.Log(logger,insertStockOutDetail);
                dal.ExecuteQuery(insertStockOutDetail);
                stockOutDetId += 1;
                // insert department-stock
                //string insertStock = "insert into stock(stock_id,product_id,product_master_id,quantity,create_date,good_quantity,create_id,del_flg) values(18854,'001419317H01','0000000014193',1,'2009-07-27 00:00:00',1,'admin',0)";
                // stock
                dal.ExecuteQuery(" update department_stock "
                              + " set quantity = quantity - " + outDetail.Quantity + " , "
                              + " good_quantity = good_quantity - " + outDetail.Quantity + " "
                              + " where product_id = '" + outDetail.Product.ProductId + "' "
                              + " and department_id = " + department.DepartmentId);

            }

            return true;
        }
예제 #4
0
        public void MakeRawDepartmentStockOut(Department department, DepartmentStockOut stockOut, DepartmentPrice price)
        {
            DataAccessLayer dalSubStock = new DataAccessLayer(Properties.Settings.Default.SubStockDB);
            DataAccessLayer dalSalePoint = new DataAccessLayer(Properties.Settings.Default.SalePointDB);

            //DataAccessLayer dalSalePoint = new DataAccessLayer("achay");

            /*try
            {*/

            try
            {

                DepartmentStockIn stockIn = new FastDepartmentStockInMapper().Convert(stockOut);
                // get max stock in id
                string deptStr = "";
                string extraZero = "";
                string startNum = "";

                if(department.DepartmentId > 999)
                {
                    deptStr = department.DepartmentId.ToString();
                    extraZero = "000";
                    startNum = "001";
                }
                else
                {
                    deptStr = string.Format("{0:000}", department.DepartmentId);
                    extraZero = "00000";
                    startNum = "00001";
                }

                string dateStr = DateTime.Now.ToString("yyMMdd");
                var selectMaxIdSQL = " select max(stock_in_id) from department_stock_in where stock_in_id > '" + dateStr + deptStr + extraZero + "'";

                ServerUtility.Log(logger, selectMaxIdSQL);
                //var maxId = dalSalePoint.GetSingleValue(selectMaxIdSQL);
                var maxId = dalSalePoint.GetSingleValue(selectMaxIdSQL);
                string stockInId = "";
                if(maxId == null || maxId.ToString() == string.Empty)
                {
                    stockInId = dateStr + deptStr + startNum;
                }
                else
                {
                    stockInId = string.Format("{0:00000000000000}", (Int64.Parse(maxId.ToString()) + 1));
                }

                // search in department_stock_in_history
                string selectHistory = " select stock_in_id from department_stock_in_history "
                                       + " where SOURCE_DEPARTMENT_ID = " + stockOut.DepartmentStockOutPK.DepartmentId
                                       + " and stock_out_id = " + stockOut.DepartmentStockOutPK.StockOutId;
                ServerUtility.Log(logger, selectHistory);
                var existStockInId = dalSalePoint.GetSingleValue(selectHistory);
                if (existStockInId == null || existStockInId.ToString() == string.Empty)
                {
                    string insertHistory = " insert into department_stock_in_history(stock_out_id,source_department_id,stock_in_id,dest_department_id,description,create_id,create_date,update_id,update_date ) values( "
                                           + stockOut.DepartmentStockOutPK.StockOutId + ","
                                           + stockOut.DepartmentStockOutPK.DepartmentId + ",'"
                                           + stockInId + "',"
                                           + stockIn.DepartmentStockInPK.DepartmentId + ",'"
                                           + " Xuat hang xuong cua hang " + "',"
                                           + "'admin'" + ","
                                           + "'" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "',"
                                           + "'admin'" + ","
                                           + "'" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "');";
                    ServerUtility.Log(logger, insertHistory);
                    dalSalePoint.ExecuteQuery(insertHistory);
                }
                else
                {
                    InformMessage(stockOut.DepartmentStockOutPK.StockOutId,SUBTODEPT, true,
                    stockOut.DepartmentStockOutPK.StockOutId + " đã đến " + stockOut.OtherDepartmentId + " trước đó. Không thể truyền lại !");
                    return;
                }

                DoStockIn(dalSalePoint, department, stockIn, true);
                InformMessage(stockOut.DepartmentStockOutPK.StockOutId,SUBTODEPT, false,
                    stockOut.DepartmentStockOutPK.DepartmentId +  " đã truyền "+ stockOut.DepartmentStockOutPK.StockOutId +" xuống " + stockOut.OtherDepartmentId + " thành công !");
            }
            catch (Exception exception)
            {
                ServerUtility.Log(logger,exception.Message);
                ServerUtility.Log(logger, exception.StackTrace);
                InformMessage(stockOut.DepartmentStockOutPK.StockOutId,SUBTODEPT, true,
                    stockOut.DepartmentStockOutPK.DepartmentId + " đã truyền " + stockOut.DepartmentStockOutPK.StockOutId + " xuống " + stockOut.OtherDepartmentId + " thất bại !");
            }
        }